topic Re: Calculated field in the script load in QlikView

Calculated field in the script load

Wed, 04 Feb 2015 17:40:19 GMT

Hello,

I have two tables that I have managed to merge using the join function in the script editor.

Now I want to have an extra column in this table that should be calculated as the number of times that a certain criteria is met. The problem is that the two fields involved in the calculation come from different tables (the ones that have been merged).

Here's the example:

SKU DATE Qty Planned Qty Real OTIF

A 01 2 3 1

B 01 4 3 0

C 01 5 5 1

The OTIF logic is the following: if Real is equal or higher than Planned then it's 1, if not is 0. Qty Planned and Qty Real each come from different tables.

What I try to do is to apply this calculation in the script load but I don't know where to put it in the load order. I always get the message that one field is missing (because Planned and Real come from diff tables).

Does anybody have any ideas on how to do this?

Thank you in advance.

Re: Calculated dimension in the script load

sunny_talwar — Wed, 04 Feb 2015 17:45:14 GMT

Add this to your script:

Temp:

Load SKU,

Date,

[Qty Planned]

Resident XYZ;

Join(Temp)

Load SKU,

Date,

[Qty Real]

Resident ABC;

Join(XYZ or ABC)

Load SKU,

Date,

Qty Planned (if joining it to XYZ)

If([Qty Real] >= [Qty Planned], 1, 0) as OTIF

Resident Temp;

Drop Table Temp;

Hope this will be helpful.

Best,

Re: Calculated field in the script load

maxgro — Wed, 04 Feb 2015 17:55:03 GMT

another solution (without join) could be

MapB:

mapping load SKU & '-' & DATE as ID, [Qty Real] inline [

SKU , DATE , Qty Real

A , 01, 3

B , 01, 3

C , 01, 5

];

load SKU, DATE, [Qty Planned],

If(ApplyMap('MapB', SKU & '-' & DATE) >= [Qty Planned], 1, 0) as OTIF

inline [

SKU , DATE , Qty Planned

A , 01, 2

B , 01, 4

C , 01, 5

];

Re: Calculated field in the script load

rwunderlich — Wed, 04 Feb 2015 18:04:33 GMT

A simple method is to make another pass through the data after the JOIN.

Tab1:

LOAD ....

JOIN (Tab1) LOAD ...;

Tab1A:

LOAD

If([Qty Real] >= [Qty Planned], 1, 0) as OTIF

RESIDENT Tab1;

DROP TABLE Tab1;

-Rob

Re: Calculated dimension in the script load

Thu, 05 Feb 2015 12:20:46 GMT

Hello,

Thank you for your reply.

I'm affraid I cannot get it to work this way.

I'll upload a simple sample file. Could you please check what's wrong the script?

Thank you.

Re: Calculated field in the script load

Thu, 05 Feb 2015 12:30:31 GMT

This did the trick.

Thank you very much.

Re: Calculated field in the script load

Thu, 05 Feb 2015 12:32:13 GMT

I'm not quite clear on how to implement in the script load.

It may be much advanced knowledge to my poor understanding of QV.

This does not mean that I wouldn't like to know how to do this.

Thank you.

Re: Calculated field in the script load

qlikcook — Wed, 21 Oct 2020 07:58:09 GMT

Thanks for your contribution.

Solved a tricky situation for me regarding a field color condition (using a date calculation).

Making the date calculation a field instead solved that issue.