Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Experts,
My load script is loading two tables SALES and VIP from Excel, would it be possible to add a new field to SALES by IF statements referencing to VIP during LOAD? The reason for doing this, is that VIP's ExpiryDate updates everyday, so I need a way (the NewField) during LOAD to mark the membership status of each TicketID on that TxDate. Below is a sample of how they look like. Thank you in advance!
SALES:
VipID | TxDate | TicketID | Sales |
V0019 | 20191231 | 201912310001 | 200 |
V0203 | 20191231 | 201912310002 | 100 |
V0345 | 20191231 | 201912310003 | 350 |
V2897 | 20191231 | 201912310004 | 200 |
VIP:
VipID | ExpiryDate | CreateDate |
V0019 | 20201230 | 20160729 |
V0203 | 20201230 | 20181231 |
V0345 | 20201230 | 20191231 |
V2897 | 20201230 | 20191231 |
IF Statements:
If(ExpiryDate-TxDate=364 AND TxDate-CreateDate>365,'Reactivated'),
if(ExpiryDate-TxDate=364 AND TxDate-CreateDate=365, 'Renewal'),
if(ExpiryDate-TxDate=364 AND TxDate-CreateDate=0, 'Newjoin'),
Below is the expected outcome after adding a new field to SALES during LOAD
Outcome:
VipID | TxDate | TicketID | Sales | NewField |
V0019 | 20191231 | 201912310001 | 200 | Reactivated |
V0203 | 20191231 | 201912310002 | 100 | Renewal |
V0345 | 20191231 | 201912310003 | 350 | Newjoin |
V2897 | 20191231 | 201912310004 | 200 | Newjoin |
Try this:
Script:
Sales:
load VipID,Date#(text(TxDate),'YYYYMMDD')AS TxDate,TicketID,Sales inline [
VipID TxDate TicketID Sales
V0019 20191231 201912310001 200
V0203 20191231 201912310002 100
V0345 20191231 201912310003 350
V2897 20191231 201912310004 200
](delimiter is ' ');
left join
VIP:
load VipID,Date#(text(ExpiryDate),'YYYYMMDD')as ExpiryDate ,Date#(text(CreateDate),'YYYYMMDD')as CreateDate inline[
VipID ExpiryDate CreateDate
V0019 20201230 20160729
V0203 20201230 20181231
V0345 20201230 20191231
V2897 20201230 20191231
](delimiter is ' ');
tempSales:
load *,ExpiryDate-TxDate as Cond1,
TxDate-CreateDate as Cond2,
If(ExpiryDate-TxDate=365 AND TxDate-CreateDate>365,'Reactivated',
if(ExpiryDate-TxDate=365 AND TxDate-CreateDate=365, 'Renewal',
if(ExpiryDate-TxDate=365 AND TxDate-CreateDate=0, 'Newjoin',null())))as NewField
resident Sales;
drop table Sales;
rename table tempSales to Sales;
exit script;
Try this:
Script:
Sales:
load VipID,Date#(text(TxDate),'YYYYMMDD')AS TxDate,TicketID,Sales inline [
VipID TxDate TicketID Sales
V0019 20191231 201912310001 200
V0203 20191231 201912310002 100
V0345 20191231 201912310003 350
V2897 20191231 201912310004 200
](delimiter is ' ');
left join
VIP:
load VipID,Date#(text(ExpiryDate),'YYYYMMDD')as ExpiryDate ,Date#(text(CreateDate),'YYYYMMDD')as CreateDate inline[
VipID ExpiryDate CreateDate
V0019 20201230 20160729
V0203 20201230 20181231
V0345 20201230 20191231
V2897 20201230 20191231
](delimiter is ' ');
tempSales:
load *,ExpiryDate-TxDate as Cond1,
TxDate-CreateDate as Cond2,
If(ExpiryDate-TxDate=365 AND TxDate-CreateDate>365,'Reactivated',
if(ExpiryDate-TxDate=365 AND TxDate-CreateDate=365, 'Renewal',
if(ExpiryDate-TxDate=365 AND TxDate-CreateDate=0, 'Newjoin',null())))as NewField
resident Sales;
drop table Sales;
rename table tempSales to Sales;
exit script;
I would use a rather different approach.
Firstly you need to create dates instead of numbers as conditions wouldn't work.
map_exp:
Mapping LOAD VipID,
ExpiryDate
FROM
[https://community.qlik.com/t5/New-to-QlikView/LEFT-JOIN-to-add-new-field-with-IF-statement-during-LO...]
(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @2);
map_cre:
Mapping LOAD VipID,
CreateDate
FROM
[https://community.qlik.com/t5/New-to-QlikView/LEFT-JOIN-to-add-new-field-with-IF-statement-during-LO...]
(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @2);
Data:
LOAD VipID,
Date(Date#(TxDate, 'YYYYMMDD'), 'YYYY.MM.DD') as TxDate,
TicketID,
Sales,
Date(Date#(ApplyMap('map_exp', VipID), 'YYYYMMDD'), 'YYYY.MM.DD') as ExpiryDate,
Date(Date#(ApplyMap('map_cre', VipID), 'YYYYMMDD'), 'YYYY.MM.DD') as CreateDate,
If(Date(Date#(ApplyMap('map_exp', VipID), 'YYYYMMDD'), 'YYYY.MM.DD') - Date(Date#(TxDate, 'YYYYMMDD'), 'YYYY.MM.DD') = 365, // changed from 364 to 365
If(Date(Date#(TxDate, 'YYYYMMDD'), 'YYYY.MM.DD') - Date(Date#(ApplyMap('map_cre', VipID), 'YYYYMMDD'), 'YYYY.MM.DD') > 365, 'Reactivated',
If(Date(Date#(TxDate, 'YYYYMMDD'), 'YYYY.MM.DD') - Date(Date#(ApplyMap('map_cre', VipID), 'YYYYMMDD'), 'YYYY.MM.DD') = 365, 'Renewal',
If(Date(Date#(TxDate, 'YYYYMMDD'), 'YYYY.MM.DD') - Date(Date#(ApplyMap('map_cre', VipID), 'YYYYMMDD'), 'YYYY.MM.DD') = 0, 'Newjoin', ':'))), ':') as Status
FROM
[https://community.qlik.com/t5/New-to-QlikView/LEFT-JOIN-to-add-new-field-with-IF-statement-during-LO...]
(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @1);
Sorry for my late reply, it took me awhile to update my own LOAD script to try out both approaches because there are many new skills to me. The 1st method took less efforts because it is similar to my original LOAD structure, the 2nd method required more efforts to update my LOAD script, but both methods really do the job well!
Here is some interesting stuff to share, many thanks to Arthur and Mindaugas!
* Reload time: Method-2 is nearly doubled the time for Method-1 to reload data.
* File size: Method-1 is nearly doubled the size then Method-2.
Thanks for the info. This is interesting.
Oh my bad!!!!
After checking both methods' LOAD script, I missed to load many fields for Method-2, after reloading the equal data fields as Method-1, the qvw file size increased. So please allow me to revise my findings as below, thanks! (Hopefully I'm not getting anything wrong somewhere again...)
* Reload time: Method-2 is still nearly doubled the time for Method-1 to reload data.
* File size: Method-2 qvw file is now nearly 4 times the size then Method-1.