Create table from Data Load Editor based in conditional selections of previous tables

[Anonymous]

I will simplify my question to understand it easier: I have a table called "Stores" defined by the following Qlik Code in Data Load Editor:

Stores:

Load * inline

[

id, sales, id_session, coupon

1a, 3, 1, true

2a, 1, 1, true

3a, 2, 1, false

1b, 3, 2, true

1c, 4, 3, false

];

Graphically:

id	sales	id_session	coupon
1a	3	1	true
2a	1	1	true
3a	2	1	false
1b	3	2	true
1c	4	3	false

I have a second table called "Sessions" loaded in Qlik through my code:

Sessions:

Load * inline

[

id_session, sum_sales,

1, 6,

2, 3,

3, 4,

];

Graphically:

id_session	sum_sales
1	6
2	3
3	4

And what I want to create is a new table called Results that is a modification of table "Sessions" where the column "sum_sales" is replaced by new 2 columns ("sum_with_coupon" and "sum_without_coupon") whose value depends on the value true or false of the column "coupon" of table "Stores".  This means that the sum of "sum_with_coupon" and "sum_without_coupon" in each row of table "Results" (id_session=X) must match the value of Sum(Sales) where id_session = X  in table "Stores". I know how to do it in a table generated through "Table" in Qlik Sense charts and adapting the metric. What I would like to do is generating the table in the Data Load Editor, pior to working with it through visualzations menu. Thanks in advance for your help. Anibal

id_session	sum_with_coupon	sum_without_coupon
1	4	2
2	3	0
3	0	4

[devarasu07]

Hi,

Try to create table using below set analysis expression you can able to achieve it.

dimension: id_session

measure:1 with coupon

sum( {$<coupon={'true'}>}sales)

measure:2 without coupon

sum( {$<coupon={'false'}>}sales)

P.S: refer to the attached basics of set analysis and it might useful for you

Ignore filters in tables*

Thanks,
Deva

[Anonymous]

Hi Devarasu R,

Thank you very much for your response, nevertheless it is not what I am looking for. I want to create the new table through the Data Load Editor. With this response I would not know how to create the table with the 3 fields mentioned using for example "Resident" option to make good use of the tables already loaded. Thanks in advance.

[Anonymous · Accepted solution]

Maybe something like this ;

Stores:

Load * inline

[

id, sales, id_session, coupon

1a, 3, 1, true

2a, 1, 1, true

3a, 2, 1, false

1b, 3, 2, true

1c, 4, 3, false

];

Results:

Load

id_session,

sum ( sales_with_coupon ) as sales_with_coupon ,

sum ( sales_without_coupon ) as sales_without_coupon

group by id_session

;

Load

id_session,

if ( coupon = 'true' , sales ) as sales_with_coupon ,

if ( coupon = 'false' , sales ) as sales_without_coupon

resident Stores

;

Drop Table Stores ;

[Anonymous]

Excellent @Bill Markham. With your response I see that in fact "Sessions" table isn´t actually needed as all the information required to build "Results" is stored already in "Stores". Thanks.