Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
GreatGreekYogurt
Contributor III
Contributor III

Creating categories with IF statement

Good Evening,

I have problems creating category with if statement. For one if statement it returns null value instead of string 'Lost'. I don't have an idea why.

SET vPurchaseYear=2017;

Table1:
Load
        [Customer ID],
        Year([Order Date]) as [Year],
       [Net Sales],
       [Days From Purchase],
        [Lost Customer Flag]
resident Purchases;


Left Join(Purchases)
Load
[Customer ID],
if(sum([Net Sales])<500,'Ordinary',
                    if(sum([Net Sales])<1000,'Business',
                                if(sum([Net Sales])>1000,'VIP',
                                              if(Sum([Lost Customer Flag])>=1,'Lost')))) as [Customer Category]
resident Table1
where ([Year]=$(vPurchaseYear))
GROUP BY [Customer ID];

DROP TABLE Table1;

I used Sum([Lost Customer Flag])>=1, since Group by requires aggregation for all statements and it returned Invalid expression error if I just left the field name [Lost Customer Flag].

I need 'Lost' in the same field [Customer Category] together with others 'VIP', 'Business' etc.

What could be the possible reason for giving null here? 

Labels (4)
1 Solution

Accepted Solutions
Anonymous
Not applicable

Maybe you can try this or something similar:

ft42_1-1591380732706.png

But if I were in your situation I would rather create preceding load opposed to a resident load. 

View solution in original post

2 Replies
marcus_sommer

Try it with a different order within the if-loop - means querying the [Lost Customer Flag] at first before your sum-cluster starts and also set an else-value for the case that no check has been true, for example:

Sum([Lost Customer Flag]) & '|' & sum([Net Sales]) ...

- Marcus

Anonymous
Not applicable

Maybe you can try this or something similar:

ft42_1-1591380732706.png

But if I were in your situation I would rather create preceding load opposed to a resident load.