Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Not applicable

Calculated field in the script load

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.

Tags (2)
1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Calculated field in the script load

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

View solution in original post

6 Replies

Re: Calculated dimension in the script load

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

MVP
MVP

Re: Calculated field in the script load

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

  ]; 

MVP & Luminary
MVP & Luminary

Re: Calculated field in the script load

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

View solution in original post

Not applicable

Re: Calculated dimension in the script load

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.

!

Not applicable

Re: Calculated field in the script load

This did the trick.

Thank you very much.

Not applicable

Re: Calculated field in the script load

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.