LEFT JOIN to add new field with IF statement during LOAD?

[abalert]

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

[Arthur_Fong · Accepted solution]

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;

[MindaugasBacius]

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);

[abalert]

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.

[Arthur_Fong]

Thanks for the info. This is interesting.

[abalert]

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.