Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
Keitaru
New Contributor III

How Create Buckets in load script?

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. 
RAG.png

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

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: How Create Buckets in load script?

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 ...
    ;

talk is cheap, supply exceeds demand
5 Replies
MVP & Luminary
MVP & Luminary

Re: How Create Buckets in load script?

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.

 

 


talk is cheap, supply exceeds demand
Keitaru
New Contributor III

Re: How Create Buckets in load script?

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 a
s 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.

target table.png 

 

 

 

 

 

 

 

 

MVP & Luminary
MVP & Luminary

Re: How Create Buckets in load script?

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?


talk is cheap, supply exceeds demand
Keitaru
New Contributor III

Re: How Create Buckets in load script?

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.

MVP & Luminary
MVP & Luminary

Re: How Create Buckets in load script?

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 ...
    ;

talk is cheap, supply exceeds demand