Tag: rows

to hide rows in Google Sheets based on the value of the cell in another row

I'm trying to build a spreadsheet in Google sheets with a checkbox that will hide and display lines 5 through 60.

My checkbox is in cell A4, and when this box is checked, cell A4 says "hide" and cell A4 not checked indicates "display". I've saved two macros to hide and display lines 5 to 60 and have copied the code from each to make my functions hide and show. Now, I'm trying to write an onEdit () function that will make everything work, but I'm out of my element. Any help is greatly appreciated.

Here is the code I have up to now:

hideRows5to60 () {function
var spreadsheet = SpreadsheetApp.getActive ();
spreadsheet.getRange (# 5: 60 & # 39;). activate ();
spreadsheet.getActiveSheet (). hideRows (spreadsheet.getActiveRange (). getRow (), spreadsheet.getActiveRange (). getNumRows ());
spreadsheet.getRange (61 61: 61)). activate ();
};

function showRows5to60 () {
var spreadsheet = SpreadsheetApp.getActive ();
spreadsheet.getRange (& # 39; 4: 61 & # 39;). activate ();
spreadsheet.setCurrentCell (spreadsheet.getRange (& # 39; A61 & # 39;));
spreadsheet.getActiveSheet (). showRows (3, 58);
};

onEdit function () {
var ss = SpreadsheetApp.getActiveSpreadsheet ();
var s = ss.getSheetByName (& # 39; Duct Leak & # 39;);
var cellSh = s.getRange (& # 39; A4 & # 39;). getValues ​​();
}
if (cellSh = "hide");
{
showRows5to60 ();
}
if (cellSh = "hide");
{
hideRows5to60 ();
}

Java-Selenium looking for the rows of a table in the specified date range

On my website, I can select a date range and a list of dates. My test case is to check if the listed transaction dates are within the selected date range.

This is my code. I receive all transaction dates in a user interface table.
But when I run, I get the unstoppable date: "07/16/19"
Can you help yourself? What is the problem in this code?

public boolean verifyTableDate (ElementTable WebElement, int columnNumber, String fromDate, String toDate) {
Boolean result = true;
String columnValue = "";

                WebElement argableTableElem = null;
listing TR cells;
listing the TD cells;
int ctr;
SimpleDateFormat fmt = new SimpleDateFormat ("MM / dd / yy");
SimpleDateFormat fmt1 = new SimpleDateFormat ("MMM / dd / yyyy");
Date parseFromDate;
Date parseToDate;
Date dateColumnVal;

disputeTableElem = ElementTable;
cellsTR = disputeTableElem.findElements (By.tagName ("tr"));
cellsTD = null;
print ("DATE ​​RANGE:" + fromDate + "-" + toDate);
waitUntilPageLoadingIsComplete ();
for (WebElement cellTR: cellsTR) {
cellsTD = cellTR.findElements (By.tagName ("td"));
ctr = 0;
for (WebElement cellTD: cellsTD) {
ctr ++;
if (ctr == column_number) {
columnValue = cellTD.getText (). trim (). replaceAll (" n", "");
columnValue = columnValuesubstring (0,8);
try {
dateColumnVal = fmt.parse (columnValue);
parseFromDate = fmt1.parse (fromDate);
parseToDate = fmt1.parse (toDate);

String dateColumnValString = fmt.format (dateColumnVal);

if (dateColumnVal.compareTo (parseFromDate)> = 0 && dateColumnVal.compareTo (parseToDate) <= 0) {

print (dateColumnValString + "is in the date range.");
highLightElement (cellTD);
result = true;
} other {
errorDesc + = dateColumnValString + "is not in the date range.";
result = false;
}
} catch (ParseException e) {
e.printStackTrace ();
}

}
}
}
return the result;
}

DateTimeFormatter customFormatter = DateTimeFormatter.ofPattern ("MMM / dd / YYYY");
String endDate = LocalDate.now (ZoneId.of (zoneID_EST)). Format (customFormatter);
String startDate = LocalDate.now (ZoneId.of (zoneID_EST)). MinusDays (100) .format (customFormatter); 

                DateTimeFormatter customFormatter1 = DateTimeFormatter.ofPattern ("MM / dd / yy");
String parseEndDate = LocalDate.now (ZoneId.of (zoneID_EST)). Format (customFormatter1);
String parseStartDate = LocalDate.now (ZoneId.of (zoneID_EST)). MinusDays (100) .format (customFormatter1);

Python – Pandas updating / adding columns to rows incrementally using dictionary key values

I wish to fill the columns of a data frame (df) by performing an iterative loop on a list (A_list) generating a dictionary in which the keys are the names of the desired columns of df (in this example below, the new columns are 'C', 'D' and 'E'

pandas of import
def gen_data (key):
data_dict = {C}: key + key, key, key: key + key + key
returns data_dict

A_list = ['a', 'b', 'c', 'd', 'f']
df = pandas.DataFrame ({& # 39; ['a', 'b', 'c', 'd', 'f']& # 39; & # 39 ;: B [1,2,3,3,2]})

for A_value in A_list:
data_dict = gen_data (A_value)
for data_key in data_dict:
df.loc[df.A == A_value, data_key] = data_dict[key]

So, the result should be:

df = pandas.DataFrame ({& # 39; ['a', 'b', 'c', 'd', 'e','f'],
& # 39; & # 39 ;: B [1,2,3,3,2,1],
& # 39; C & # 39 ;: ['aa','bb','cc','dd',nan,'ff'],
& # 39; & # 39 ;: RE ['a', 'b', 'c', 'd', nan,'f'],
& # 39; S & # 39 ;: ['aaa','bbb','ccc','ddd',nan,'fff']})

I feel that 

for data_key in data_dict:
df.loc[df.A == A_value, data_key] = data_dict[key]

is really inefficient if there are a lot of lines in df and I think there should be a way to remove the for loop in this code.

for A_value in A_list:
data_dict = gen_data (A_value)
for data_key in data_dict:
df.loc[df.A == key, data_key] = data_dict[key]

Google Sheets, how to iterate between columns and rows

I'm running the following forumula: 

= IF (INDEX (C $ 2: US $ 2, 1, ROW (1: 1))), CONCATENATE (C $ 2.00, "-", INDEX (C $ 1: 1, 1, ROW (1 : 1)))))

On the following table

Basically, if a value is present in column C2, I return the value + the value of B2. I'd like to do this for all columns up to O2, however, once I reach the O2 column, I'd like to go to the next line and have the process start with a new one. line. How would I do that?

mysql – Why does this join provide more rows?

This is my request:

SELECT GROUP_CONCAT (c.categoryName) categories AS
From post p
INNER JOIN join_post_cat jpc USING (postId)
INNER JOIN categories c USING (categoryId)
O p.postId = 1;

This is the output:

+ ------------------------------------------------- ---- ---- +
| categories |
+ ------------------------------------------------- ---- ---- +
| Apartment, Auditorium, Bachelor, Community Center |
+ ------------------------------------------------- ---- ---- +
1 line in the set (0.00 sec)

This is another query:

SELECT GROUP_CONCAT (a.areaName) AS slots
DE join_area ja
INNER JOIN area a ON ja.belongAreaId = a.areaId
O ja.areaId = 29;

This is the output:

+ ------------------------------------------------- ---- --------------- +
| places |
+ ------------------------------------------------- ---- --------------- +
| Dhaka Division, Dhaka Zhila, Dhaka City, Jatrabari, West Jatrabari |
+ ------------------------------------------------- ---- --------------- +
1 line in the set (0.00 sec)

These two data data are related to each other by ONE TO MANY relationship. So when I put the two queries together in one query, the results were repeated.

This is an updated query:

SELECT GROUP_CONCAT (c.categoryName) categories AS,
GROUP_CONCAT (a.areaName) AS slots
From post p
INNER JOIN join_post_cat jpc USING (postId)
INNER JOIN categories c USING (categoryId)
INNER JOIN join_area ja USING (areaId)
INNER JOIN area a ON ja.belongAreaId = a.areaId
O p.postId = 1;

This is the output:

+ ------------------------------------------------- ---- ---------------------------------------------- ---- ---------------------------------------------- ---- ---------------------------------------------- ---- ---------------------------------------------- ---- ------------ + --------------------------------- -------- ------------------------------------------ -------- ------------------------------------------ -------- ------------------------------------------ -------- ------------------------------------------ -------- ---------------- +
| categories | places |
+ ------------------------------------------------- ---- ---------------------------------------------- ---- ---------------------------------------------- ---- ---------------------------------------------- ---- ---------------------------------------------- ---- ------------ + --------------------------------- -------- ------------------------------------------ -------- ------------------------------------------ -------- ------------------------------------------ -------- ------------------------------------------ -------- ---------------- +
| Apartment, Apartment, Apartment, Apartment, Apartment, Apartment, Auditorium, Auditorium, Auditorium, Auditorium, Auditorium, Bachelor, Bachelor, Bachelor, Bachelor, Bachelor, Community Center, Community Center, Community Center, Community Center, Community Center | Dhaka Division, Dhaka Zhila, Dhaka City, Jatrabari, West Jatrabari, Dhaka Division, Dhaka Zhila, Dhaka Town, Jatrabari, West Jatrabari, Dhaka Division, Dhaka Zhila, Dhaka City, Jatrabari, West Jatrabari, Dhaka Division, Dhaka , Dhaka, Jatrabari, West Jatrabari |
+ ------------------------------------------------- ---- ---------------------------------------------- ---- ---------------------------------------------- ---- ---------------------------------------------- ---- ---------------------------------------------- ---- ------------ + --------------------------------- -------- ------------------------------------------ -------- ------------------------------------------ -------- ------------------------------------------ -------- ------------------------------------------ -------- ---------------- +
1 line in the set (0.01 sec)

When I add separate in the interior GROUP_CONCAT I have the desired output.

That's the query with the correct output:

SELECT GROUP_CONCAT (separate categories c.categoryName) AS,
GROUP_CONCAT (separate locations of a.areaName) AS
From post p
INNER JOIN join_post_cat jpc USING (postId)
INNER JOIN categories c USING (categoryId)
INNER JOIN join_area ja USING (areaId)
INNER JOIN area a ON ja.belongAreaId = a.areaId
O p.postId = 1;

Exit:

+ ------------------------------------------------- ---- ---- + ----------------------------------------- -------- ------------------- +
| categories | places |
+ ------------------------------------------------- ---- ---- + ----------------------------------------- -------- ------------------- +
| Apartment, Auditorium, Bachelor, Community Center | Dhaka Division, Dhaka Zhila, Dhaka City, Jatrabari, West Jatrabari |
+ ------------------------------------------------- ---- ---- + ----------------------------------------- -------- ------------------- +
1 line in the set (0.00 sec)

Now I want to know:

  • Why does MySQL get the same data multiple times?
  • Why do I need to add separate? (I know how to delete duplicates but why MySQL retrieves them several times)
  • Is there a better way to accomplish this task without obtaining duplicate data or without using separate or group?

larger n per group – SQLite: Leave only n rows for each field

I have a table

+ ----------------------------- + --------------- + --- -------- +
| Painter | Painting | Rate |
+ ----------------------------- + --------------- + --- -------- +
| Zinaida Serebriakova | Window | 5 |
| Zinaida Serebriakova | Florence | 1 |
| Zinaida Serebriakova | Nude | 8 |
| Zinaida Serebriakova | Bath | 4 |
| Thomas and William Daniell | Turbine machines | 2 |
| Thomas and William Daniell | George Iain | 7 |
| Thomas and William Daniell | Linen extractors | 3 |
| Robert Motherwell | Galions | 1 |
| Robert Motherwell | Ulysses | 2 |
+ ----------------------------- + --------------- + --- -------- +

I have to get 2 paintings for each painter with the highest rate.

+ ----------------------------- + --------------- + --- -------- +
| Painter | Painting | Rate |
+ ----------------------------- + --------------- + --- -------- +
| Zinaida Serebriakova | Nude | 8 |
| Zinaida Serebriakova | Window | 5 |
| Thomas and William Daniell | George Iain | 7 |
| Thomas and William Daniell | Linen extractors | 3 |
| Robert Motherwell | Ulysses | 2 |
| Robert Motherwell | Galions | 1 |
+ ----------------------------- + --------------- + --- -------- +

And delete everything else!

sharepoint online – RenderListDataAsStream – Option to limit the number of rows for groups

I use RenderListDataAsStream to get document library data with a grouped view. Once the group is expanded, I want to load all items in this group, not just the specified amount (item limit) in the display settings.

"https://contoso.sharepoint.com/sitename/_api/web/Lists/getbytitle(&#39;Title&#39;)/RenderListDataAsStream?IsGroupRender=TRUE&DrillDown=1&view=bb2f25d1-0766-a85--F17% 23Group2% 3B% 23 ";

This query will only return 30 items in Group2 & # 39; from the library. How can I replace the limit of items?

How to extract rows from a data frame using a vector with the values ​​of one of the variables in R?

I have a data frame in R and I want to extract the lines that have some value in one of its variables from the values ​​of another vector.

For example, extracting from the data frame iris the lines with the species that are in another vector criterion <- c ("mushrooms", "virginica")

mysql – The result of a query with two joins results in repeated rows whose number increases exponentially with each new insertion in the join tables

I'm trying to figure out what the calculation behind repeated lines is at two joins in the same query – it kills my backend running Sequelize:

SELECT 'history`.`id`,
`Story`.`title`,
`Story`.`themeid`,
`Industries`.`id` AS` Industries.id`,
`Industries`.`s_sub_sector` AS` Industries.sub_sector`,
`Regulators`.`id` AS` Regulators.id`,
`Regulators`.``country_or_area` AS` Regulators.country_or_area`
From story AS AS Story
LEFT OUTER JOIN (`storyindustry` AS & # 39; Industries -> StoryIndustry`
INNER JOIN 'industry` AS' Industries`
ON `Industries`.`id` =
`Industries-> StoryIndustry`.`industryid`)
ON `story`.`id` = & # 39; Industries-> StoryIndustry`.`storyid`
LEFT OUTER JOIN (`storyregulator` AS & # 39; Regulators-> StoryRegulator`
INNER JOIN & # 39; regulator & # 39; AS `Regulators`
ON `Regulators`.`id` =
`Regulators-> StoryRegulator`.`regulatorid`)
ON `story`.`id` =` Regulators-> StoryRegulator`.`storyid`
ORDER BY `story`.`id` DESC;

The query above gives the result below, where history with id 177 is repeated each time a new entry is inserted into one of the two join tables – that's the is like a Cartesian product.

+ ----- + --------------- + --------- + --------------- + - ---------------------- + --------------- + ----------- ------------------ +
| id | title | themeid | Industries.id | Industries.sub_sector | Regulators.id | Regulators.country_or_area |
+ ----- + --------------- + --------- + --------------- + - ---------------------- + --------------- + ----------- ------------------ +
| 177 | Test history 1. | 63 | 1 | Plant production | 539 | United Nations |
| 177 | Test history 1. | 63 | 1 | Plant production | 540 | United Nations |
| 177 | Test history 1. | 63 | 1 | Plant production | 541 | United Republic of Tanzania |
| 177 | Test history 1. | 63 | 2 | Plant production | 539 | United Nations |
| 177 | Test history 1. | 63 | 2 | Plant production | 540 | United Nations |
| 177 | Test history 1. | 63 | 2 | Plant production | 541 | United Republic of Tanzania |
| 177 | Test history 1. | 63 | 3 | Plant production | 539 | United Nations |
| 177 | Test history 1. | 63 | 3 | Plant production | 540 | United Nations |
| 177 | Test history 1. | 63 | 3 | Plant production | 541 | United Republic of Tanzania |
+ ----- + --------------- + --------- + --------------- + - ---------------------- + --------------- + ----------- ------------------ +

With more than 300,000 lines resulting from my complete query, MySQL moves quite fast, but the ORM, Sequelize crashes after a few seconds while trying to analyze all these lines and condense them into a single result nested.

Are you wondering if there is a way to optimize the query? Running them separately only takes a few milliseconds, but together, an eternity.

Thank you!

How to summarize only certain rows of a column, save in variables, misc misc.

I am on MySQL 8.0 and use MySQL Workbench. I have just started to learn MySQL and I have a lot of trouble understanding how to do that.

I have a table called 'business_ledger' and the following fields: employee_name (different string values ​​to designate employees – not used in this query, but this is part of the database), title (with the values: Novice, Journeyman, Master), Earnings (various int ), certification (with the values: None, In Progress, Certified) and% (this option will be created later).

The first query is to sort the total earnings by title. I give an example of what it currently looks like with simplified earnings, so the second part makes more sense. The total gains appear to be correct based on the values ​​of the database.

(Table 1)

Title | Earnings

Novice | 100

Companion | 100

Master | Now, I also have to separate the title by certification. In addition to that, I need a percentage of each certification in each group of titles. I do not know how to do it.

The expected release should be something like this (I've had the sum of the earnings of each title up to $ 100 just for the purposes of the example). The field "Percentage" must also total 100% for each title, not for all income, etc .:

(Table 2)

Title | Certification | The gains | Percent

Novice | None | 25 | 0.25
SEMrush

Novice | In progress | 60 | 0.60

Novice | Certified | 15 | 0.15

Companion | None | 20 | 0.20

Companion | In progress | 40 | 0.40

Companion | Certified | 40 | 0.40

Master | None | 35 | 0.35

Master | In progress | 15 | 0.15

Master | Certified | 50 | 0.50 The second query concerns everything I've got and seems to separate each title by certification (a bit like above). However, this does not seem to work properly because in some cases the earnings column contains "0". I know that in the database, some 'employee_name have & # 39; 0 for their income. MySQL does not seem to total all of this, and it seems rather that if there is a 0 in the revenue field of a certification, it generates a profit for this 0 certification without adding the other non-zero values. I am not sure to explain this well, so an example is presented below (I do not know the percentage since I do not have it yet).

(Table 3)

Title | Certification | The gains |

Novice | None | 0 |

Novice | In progress | 60 |

Novice | Certified | 15 |

Companion | None | 20 |

Companion | In progress | 0 |

Companion | Certified | 40 |

Master | None | 35 |

Master | In progress | 15 |

Master | Certified | 0 | But I know that there are novice employees, without certification, who have earned something, but these are not taken into account for any reason. So, for example, I know that there is 100% win for "Novice", but my query only displays 75 for any reason.

I must also tabulate the winnings of each title and determine the percentage of each title that has a specific certification and mark it as its own "percentage" column. I know the percentage will have to be something like: "(credits_of_certification) / (total_earnings_of_title) in percentage", but I'm not sure how to get the total_earnings_of_title without getting the total income from -all- titles. I guess it's something like:

sum (earnings) when title = "Novice" as Novice_Earnings; sum (earnings) when title = 'Companion & # 39; as Companion_Earnings; sum (earnings) when title = & # 39; Master & # 39; as Master_Earnings; But I'm not sure how to translate this pseudo-code into MySQL, or how to extract data simply from the Winner's "Novice, Current" field. / Novice_Earnings to get the percentage.

First SELECT code title, sum (earnings) in earnings FROM business_ledger GROUP BY title 2nd code SELECT title, certification, earnings FROM business_ledger WHERE certification in (SELECT certification FROM business_ledger) GROUP BY title, certification ORDER BY title, certification Ideally, I would like to get something like Table 2, without SQL ignoring some cases where the value is 0 (as in Table 3). I'm essentially trying to make sure to total some rows of a column (all incomes of a novice employee whose certification is none, for example) and then divides some of them. " cells "in a column (like Novice-> No wins and line by this total for all novice wins.