topic Re: Script Building Help for a Logic! in QlikView

Script Building Help for a Logic!

dmohanty — Wed, 04 Mar 2015 22:15:38 GMT

Hi All.

I am bit Struggling to build a Script for the Below Logic:

I have 2 Set of Tables as below:

Driving table (Table A):

Joining Table (Table B):

The Logic to build is :

From Table A, for a single DOC_NUM and DOC_NUM_ID, find the SUM(RCV_QTY) -- It should be a single line for above example.
If SUM(RCV_QTY) = ORDER_QTY (from Table B), then proceed to next step. If doesn't matches, then Step Ends. -- For above example, it should match as '19'.
From Table A, find the MAX(POST_DATE) --- It is 1/28/2015.
If (MAX(POST_DATE) - MIN(POST_DATE) <=1 , 'YES' , 'NO') as FILL_RATE --- Here it would be more than 1 and NO.

Could someone please help in building the script for above logic?

Re: Script Building Help for a Logic!

Anonymous — Wed, 04 Mar 2015 22:35:17 GMT

Here is the logic:

Table:
LOAD
DOC_NUM,
DOC_NUM_ID,
sum(RCV_QTY) as SUM_RCV_QTY,
if(max(POST_DATE)-min(POST_DATE)<=1,'YES','NO') as FILL_RATE
FROM TableA
GROUP BY DOC_NUM,DOC_NUM_ID;

LEFT JOIN (Table) LOAD
DOC_NUM,
DOC_NUM_ID,
ORDER_QTY,
RCV_DATE
FROM TableB;

LEFT JOIN (Table) LOAD
DOC_NUM,
DOC_NUM_ID,
if(SUM_RCV_QTY=ORDER_QTY, 'Next Step', 'End') as "What to do next" // up to you what to do about that...
RESIDENT Table;

Re: Script Building Help for a Logic!

maxgro — Wed, 04 Mar 2015 22:36:38 GMT

if you can post some data, we can try

the psudo code could be

1) tmp1: load doc_num, doc_num_id, sum(rcv_qty), min(post_date), max(post_date) resident TableA

group by doc_num, doc_num_id;

2) join (tmp1) load * resident TableB; join is by doc_num, doc_num_id

3) now we have all the fields for the check on the same record; do a resident load of tmp1 for the 2 check

If SUM(RCV_QTY) = ORDER_QTY

If (MAX(POST_DATE) - MIN(POST_DATE) <=1 , 'YES' , 'NO')