How Create Buckets in load script?

[Keitaru]

Hi

I'm trying to create 4 new columns with its own buckets within one of my data set tables using load script. Also still new to QilkSense scripting and would like to know with Data load script, how can I re-created the below based on what I've made in Excel. 

Data set has [Priority] , [Status] and 2 specific dates TD and CD columns where this logic is based on, with each status having its own calculations as show in the code below

Logic:

if Priority = 'Low' , if( check status and today vs CD+int , Color) ,if ( check status and TD vs CD+int, color))

Code:

//Low (reapeat for the other Priority (Medium to Critical))

IF([Priority] = 'Low',IF([Status*]='Under Review'And TODAY()>=[CD]+7 OR [Status]='Assigned' and TODAY()>= date(num([CD])+7.0),'RED',
IF([Status]="Under Review" and TODAY()>=date(num([CD])+5.0) or [Status*]="Assigned" and TODAY()>=date(num([CD])+5.0),'Amber',
IF([Status]="Under Review"and TODAY()<=date(num([CD])+7.0) or [Status*]="Assigned" and TODAY()<=date(num([CD])+5.0), 'Green',

IF([Status]="Under Investigation" and [TD]>=date(num([CD])+120.0) or [Status]="Pending"and [TD]>=date(num([CD])+390.0),'RED',
IF([Status] ="Under Investigation" and [TD]<=date(num([CD])+120.0) or [Status]="Pending" and [TD]<=date(num([CD])+390.0),'AMBER',
IF([Status]="Under Investigation" and [TD]<=date(num([CD])+90.0) or [Status]="Pending" and [TD]<=date(num([CD])+360.0),'Green',''))))))) as RAG_Low

Cheers!
Keitaru

[Gysbert_Wassenaar]

Make sure to group your conditions together correctly. I think you mean this:

IF([Priority] = 'Low',
    IF( ([Status*]='Under Review' And TODAY()>=[CD]+7)
        OR ([Status]='Assigned' and TODAY()>= date(num([CD])+7.0) ) ,'RED',

But it could be read as this as well:

IF([Priority] = 'Low',
    IF( [Status*]='Under Review'
        And (TODAY()>=[CD]+7 OR [Status]='Assigned')
        and TODAY()>= date(num([CD])+7.0 ) ,'RED',

So, be explicit and use parentheses to group your conditions together.

[Keitaru]

Yes the first  is what I mean

Load *,
IF([Priority] = 'Low',
    IF([Status*]='Under Review'And TODAY()>=[CD]+7 
        OR [Status]='Assigned' and TODAY()>= date(num([CD])+7.0),'RED',
                IF([Status]="Under Review" and TODAY()>=date(num([CD])+5.0) 
                    OR [Status*]="Assigned" and TODAY()>=date(num([CD])+5.0),'Amber',
                           IF([Status]="Under Review"and TODAY()<=date(num([CD])+7.0) 
                                OR [Status*]="Assigned" and TODAY()<=date(num([CD])+5.0), 'Green', 
                                        IF([Status]="Under Investigation" and [TD]>=date(num([CD])+120.0) 
                                             OR [Status]="Pending"and [TD]>=date(num([CD])+390.0),'RED',
                                                    IF([Status] ="Under Investigation" and [TD]<=date(num([CD])+120.0) 
                                                         OR [Status]="Pending" and [TD]<=date(num([CD])+390.0),'AMBER',
                                                                 IF([Status]="Under Investigation" and [TD]<=date(num([CD])+90.0) 
                                                                      OR [Status]="Pending" and [TD]<=date(num([CD])+360.0),'Green',''))))))) as RAG_Low,

Would like to know with this how can I build my column bucket? And even add  to my existing data table as I'm looking to load a table with to look like the one below at least. So that I'm able to build my dashboards from it in the Sheets portion of the app.

 

[Gysbert_Wassenaar]

I'm afraid I don't understand your problem. You have 1 expression that calculates the values for a field you call RAG_low. Isn't that what you want for the LOW column? And if you know how to do it for that column then what's the problem doing the same for the other columns?

[Keitaru]

I'd like to know what would the syntax for the load script be like. I mean i know what i want to achieve but I don't exactly know how I'm suppose to write it in Qlik Data Load Editor that i would achieve my design.

[Gysbert_Wassenaar · Accepted solution]

Well, what you posted looks ok, except for the double quotes around what are probably not field names, but field values and should therefore be single quotes instead.

If all the fields that are needed to calculate the RAG values exists in the same table and you use the exact case sensitive names then it should be ok.

MyTable:
LOAD
    ... some fields ...,
    ... big nested if-statement to calculate RAG_low ...,
    ... other big nested if's for the other RAG_* fields ...,
    ... perhaps some more fields ...
FROM
    ... source table that contains all the fields necessary to calculate all the RAG_* fields ...
    ;