topic Re: Count multiple rows as a single row OR how to flatten a table in App Development

Count multiple rows as a single row OR how to flatten a table

gal_polak — Fri, 02 Apr 2021 08:04:22 GMT

Hi,

I have a row data table with that indicates login per user in each month,

i also in the load script calculated 4 additional columns (W1 , W2, W3, W4) to indicate in which week on the month the login was made.

now i wounder what is the best approach OR what is do able and how:

1. should i flat the table so instead of having 4 rows for user that logged in each week of the month i will have a single row?

if so how to i do that in the load script in the simplest way?

2. Can i just count the user with a condition in set analysis?

here is what i have, and i added in paint the lines (counts) that i want to do in the line chart

hope my question is clear 🙂

Re: Count multiple rows as a single row OR how to flatten a table

Or — Mon, 19 Aug 2019 14:37:35 GMT

Yes to both questions.

You should easily be able to do this in script:

Load UserID, Month, Field1, Field2, Count(W1) as W1, Count(W2) as W2, Count(W3) as W3, Count(W4) as W4

From YourTable

GROUP BY UserID, Month, FIeld1, FIeld2;

If you prefer, you can do a simple user count per week even if it's four rows rather than one row. Just count(W1)+Count(W2)+Count(W3)+Count(W4).

Re: Count multiple rows as a single row OR how to flatten a table

gal_polak — Mon, 19 Aug 2019 16:19:05 GMT

Sorry but i think i didn't make my self clear,
I would like to do distinct count of the "LoginLog.UserId"
so basically i will be counting the users each week in 4 segments,

1 . count distinct number of users that have 1 only in W1
2. count distinct number of users that have 1 in W1 and W2
3. count distinct number of users that have 1 in W1 and W2 and W3
4. count distinct number of users that have 1 in W1 and W2 and W3 and W4

is it possible with set analysis?

Re: Count multiple rows as a single row OR how to flatten a table

Or — Tue, 20 Aug 2019 08:18:29 GMT

If you pre-calculated these in the script, you should just be able to check them. If you didn't fully pre-calculate (that is, you didn't check the "only" aspect, just the yes/no for each one individually), you should be able to do this in set analysis with the '-' operator (Exclusion - first but not the other). I can't test this as I don't have your data but the code should look something like this:

Count(DISTINCT {< W1 ={1} , W2 -= {1}, W3 -= {1}, W4 -= {1} >)

Here's a Henric post about this aspect of set analysis that shows a couple of other approaches and explains the differences:

https://community.qlik.com/t5/Qlik-Design-Blog/Excluding-values-in-Set-Analysis/ba-p/1471704