Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
baarathi
Creator III
Creator III

Custom Column in Load Script based on other columns

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

  

Labels (2)
1 Solution

Accepted Solutions
Kushal_Chawda

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;

View solution in original post

10 Replies
Kushal_Chawda

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
Creator III
Creator III
Author

@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
Contributor
Contributor

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
Creator III
Creator III
Author

@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
Creator III
Creator III
Author

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