Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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 ;
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
Thanks,
Deva
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.
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 ;
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.