Custom Column in Load Script based on other columns

[baarathi]

I need to create a custom column, such that when the customer does sales for first time, value in a column should be populated as 1 and the next time when he does value should be 0.

See the below sample input and output Data.

Input Data :

CusNumber	InvoiceDate	SalesAmount
Cus001	1-Jan-20	100
Cus002	2-Jan-20	200
Cus003	3-Jan-20	600
Cus001	4-Jan-20	500
Cus004	5-Jan-20	300
Cus005	6-Jan-20	200
Cus002	7-Jan-20	700
Cus003	8-Jan-20	900

Output Data :

CusNumber	InvoiceDate	SalesAmount	CustomerType
Cus001	1-Jan-20	100	1
Cus002	2-Jan-20	200	1
Cus003	3-Jan-20	600	1
Cus001	4-Jan-20	500	0
Cus004	5-Jan-20	300	1
Cus005	6-Jan-20	200	1
Cus002	7-Jan-20	700	0
Cus003	8-Jan-20	900	0

[Kushal_Chawda · Accepted solution]

try below

T1:
LOAD
    CusNumber,
    date#(InvoiceDate,'DD-MMM-YY') as InvoiceDate,
    SalesAmount
FROM [lib://Qlik]
(html, utf8, embedded labels, table is @1);

T2:
Load *,
    if(CusNumber<>Peek(CusNumber),1,0) as CustomerType
Resident T1
Order by CusNumber,InvoiceDate;

Drop Table T1;

[baarathi]

@Kushal_Chawda  Is there a way to use order by without using resident load?

[Kushal_Chawda]

No. there are other as well but for that too resident will be required. But this is easiest way you can do it

[Kushal_Chawda]

other option will be tu use expression in chart instead of using script logic

[Alexis_Tan]

Hello,

Try adding this line,

if(not Exists(CusNumber),1,0) as CustomerType

Sample:
LOAD CusNumber,
InvoiceDate,
SalesAmount,
if(not Exists(CusNumber),1,0) as CustomerType
FROM
SourceData;

[Kushal_Chawda]

@Alexis_Tan  This will work only when your data is already ordered by CustNumber and Invoice Date. @baarathi  You can use what @Alexis_Tan  suggested if your data is already ordered 

[baarathi]

@Kushal_Chawda  Thank You, I had this similar approach. But I wanted to achieve it without resident load in Script.

[Kushal_Chawda]

@baarathi  Yes if it was possible without resident then definitely I would have, but I assumed your data is not pre-ordered. Still if your data is pre-ordered then no need to take resident and you can use what @Alexis_Tan  suggested

[baarathi]

@Alexis_Tan  Thank You. @Kushal_Chawda  as you said it only works when the data is sorted.