Calculated field in the script load

[#1]

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.

[sunny_talwar]

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,

S

[maxgro]

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

  ];

a:

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

  ]; 

[rwunderlich · Accepted solution]

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

[#5]

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.

!

[#6]

This did the trick.

Thank you very much.

[#7]

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.

[qlikcook]

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.