Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello all,
I am fairly new to Qlik, and I am attempting to assign and display a Sales Group designation for each ID in my Purchasers table. My tables look something like this and are imported via flat file:
Purchasers:
CustomerID Name Region
1 John A
2 Jane G
3 Jacob X
.
.
.
Transactions:
TransactionID CustomerID Name Amount
1 2 Jane 40
2 1 Jacob 100
3 2 Jane 20
4 2 Jane 10
.
.
.
In the data load editor, I want to create a column that assigns a group to each CustomerID based on definitions created using IF THEN ELSEIF statements in the Purchasers table. I use the Transactions table to append some calculated transaction summaries to the Purchasers table (which works), and I need to use those calculated columns for the sGroup classification as well. When I am done with the Transaction table summaries, I am dropping the Transactions table. This is similar to what I have so far:
Load * From Purchasers;
Load * From Transactions;
---script that does transaction calculations per purchaser, columns are now loaded to Purchasers table---
DROP TABLE Transactions;
FOR EACH x in FieldValueList('CustomerID')
LET sGroup=Null();
IF Region='A' AND sTotal>50 THEN
LET sGroup=010;
Concatenate
GROUP:
LOAD x AS [CustomerID], sGroup AS [Sales Group];
ELSEIF Region='B' AND sTotal > 50 THEN
LET sGroup= 020;
Concatenate
GROUP:
LOAD x AS [CustomerID], sGroup AS [Sales Group];
.
.
.
ELSEIF Region='AA' AND sAvg>100 THEN
LET sGroup= 270;
Concatenate
GROUP:
LOAD x AS [CustomerID], sGroup AS [Sales Group];
ELSE
ENDIF
NEXT ID
All of the other community posts I have found are resorting back to nested if statements in the initial table load, but I have so many different groups that need to be assigned and my definitions are also based on other calculations from the Transactions table. I have written something similar in SQL using the CASE statement, and the IF THEN ELSEIF seems to be Qlik's closest match to that. The script loads just fine, but I do not see the Sales Group column in my final Purchasers table. I am not sure if I need to create this GROUP temp table to concatenate/left join to my Purchasers table or if there is a way to INLINE this sGroup value (which is fixed for the logic definitions) into the Purchasers table for the current CustomerID that the For Each Loop is currently evaluating.
Any help would be greatly appreciated! Thank you in advance!!!
Try something like this,
Load * Resident Purchasers;
Left Join (Purchasers)
Load * Resident Transactions;
Left Join (Purchasers)
Load *, If(Region='A' AND sTotal>50,010,
If(Region='B' AND sTotal > 50,020,
If(Region='AA' AND sAvg>100,270)
)
) As [Sales Group]
Resident Purchasers;