Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
Luka
New Contributor III

Creating a calculated field using another calculated field in load script

Hello community,

I am trying to create a calculated field with the use of a previous calculated field 

When i create this and Load the data, QLIK gives me an error that the "previous Calculated field" was not found

 

Example (where [TOTAL DURATION] is the field i am creating in "1" and trying to use in "2")

Calculated Field number 1:

IF([ATA DATE]='','',IF([POD DATE]='','',IF(NETWORKDAYS([ATA DATE]+1,[POD DATE]+1)<0,0,NETWORKDAYS([ATA DATE]+1,[POD DATE]+1)))) AS [TOTAL DURATION],

Calculated Field number 2:

    IF([TOTAL DURATION]='','',
    IF([TOTAL DURATION]<=3,'0 TO 3',
    IF([TOTAL DURATION]<=5,'4 TO 5',
    IF([TOTAL DURATION]<=7,'6 TO 7',
    IF([TOTAL DURATION]<=15,'8 TO 15',
    IF([TOTAL DURATION]>15,'OVER 15')))))) AS [CLOSED BUCKETS]

 

Any help is appreciated

Thanks

Labels (2)
1 Solution

Accepted Solutions
Partner
Partner

Re: Creating a calculated field using another calculated field in load script

Try removing the calculated field which is using another calculated field from the load script and instead put it as a preceding load.  See attached.

Sandeep Sarawgi
9 Replies
ajaykakkar93
Contributor III

Re: Creating a calculated field using another calculated field in load script

Hi,
can you share the load script or example file to work with
Regards & Thanks,
Ajay Kakkar
ajaykakkar93@gmail.com
MVP & Luminary
MVP & Luminary

Re: Creating a calculated field using another calculated field in load script

It seems that you try to call it within a single load-statement which isn't possible because only fields which exists within the source are valid fields. Quite common for such cases is the use of a preceeding load. This means something like:

load *, if([TOTAL DURATION] ....) as [CLOSED BUCKETS];
load *, if(...) as [TOTAL DURATION] from Source;

- Marcus

ajaykakkar93
Contributor III

Re: Creating a calculated field using another calculated field in load script

Hi,

Here is an example Please try this,

 

 

T1:
load * Inline [
A,B
a,2
b,10
c,7
];


TableF1:
load A,
sum(CalB) as CalB2
Group by A;
load A,
sum(B) as CalB
Resident T1
Group By A;

drop table T1;

Regards & Thanks,
Ajay Kakkar
ajaykakkar93@gmail.com
ajaykakkar93
Contributor III

Re: Creating a calculated field using another calculated field in load script

i have added a group by in Preceding LOAD also.
Regards & Thanks,
Ajay Kakkar
ajaykakkar93@gmail.com
Luka
New Contributor III

Re: Creating a calculated field using another calculated field in load script

Thanks Marcus,

Do I need to load my entire data under the "Load *" or only the calculated fields?

Sounds simple enough, I will give it a try in the meantime

Luka

MVP & Luminary
MVP & Luminary

Re: Creating a calculated field using another calculated field in load script

There may occasions in which it might be useful to split the loadings into multiple tables which are later merged or not but usually it includes the entire load. A bit more background to it could you find here: Preceding-Load.

- Marcus

Luka
New Contributor III

Re: Creating a calculated field using another calculated field in load script

Thank you for the detailed response

I've attached my script - honestly very new to scripting and not sure how to use the preceding load and drop table to achieve my goal

Thanks again

Luka

Partner
Partner

Re: Creating a calculated field using another calculated field in load script

Try removing the calculated field which is using another calculated field from the load script and instead put it as a preceding load.  See attached.

Sandeep Sarawgi
Luka
New Contributor III

Re: Creating a calculated field using another calculated field in load script

Thank you so much for your help !!