Creating a calculated field using another calculated field in load script

[Luka]

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

[ajaykakkar93]

Hi,
can you share the load script or example file to work with

[marcus_sommer]

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]

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;

[ajaykakkar93]

i have added a group by in Preceding LOAD also.

[Luka]

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

[marcus_sommer]

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]

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

[sandeep_sarawgi · Accepted solution]

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.

[Luka]

Thank you so much for your help !!