Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

anibal_marsis
New Contributor II

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

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:

idsalesid_sessioncoupon
1a31true
2a11true
3a21false
1b32true
1c43false

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_sessionsum_sales
16
23
34

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_sessionsum_with_couponsum_without_coupon
142
230
304
1 Solution

Accepted Solutions

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

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 ;

4 Replies
devarasu07
Honored Contributor II

Re: Sum of a measure with conditional aggregation

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

anibal_marsis
New Contributor II

Re: Sum of a measure with conditional aggregation

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.

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

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 ;

anibal_marsis
New Contributor II

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

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.

Community Browser