Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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;
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;
@Kushal_Chawda Is there a way to use order by without using resident load?
No. there are other as well but for that too resident will be required. But this is easiest way you can do it
other option will be tu use expression in chart instead of using script logic
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;
@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
@Kushal_Chawda Thank You, I had this similar approach. But I wanted to achieve it without resident load in Script.
@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
@Alexis_Tan Thank You. @Kushal_Chawda as you said it only works when the data is sorted.