Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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
];
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
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.
!
This did the trick.
Thank you very much.
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.
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.