How To Solve This Scenario On Script

[#1]

Dear All,

I am trying to find out outstanding / Account Recivables details on script.

For this I have 2 tables in first table I have customers debit balance on a particular date - say today's date, in second table I have Customers debit transaction detals

custBalanceTable:

custId	custBal
a1	90000
a2	40000

custTrnDetalsTable:

custId	Inv. No.	Inv. Date	Inv. Amount
a1	INV-80	10/10/2012	25000
a1	INV-75	09/10/2012	25000
a1	INV-50	05/10/2012	25000
a1	INV-47	04/10/2012	35000
a1	INV-44	03/10/2011	40000
a2	INV-79	10/10/2012	20000
a2	INV-78	09/10/2012	15000
a2	INV-65	06/10/2012	3000
a2	INV-45	03/10/2012	10000
a2	INV-30	15/09/2012	20000

with the help of these 2 tables I want to create a intermedieate result Table

intermedieateTable:

custId	Inv. No.	Inv. Date	Inv. Amount	Cust. Balance	Calculation	O/s Amt	O/s	Remark
a	b	c=( b-a )	Days
a1	INV-80	10/10/2012	25000	90000	65000	25000	2	Full Inv O/s
a1	INV-75	09/10/2012	25000	65000	40000	25000	3	Full Inv O/s
a1	INV-50	05/10/2012	25000	40000	15000	25000	7	Full Inv O/s
a1	INV-47	04/10/2012	35000	15000	-20000	15000	8	Partial Inv O/s
a1	INV-44	03/10/2011	40000	-20000	-60000	0	9
a2	INV-79	10/10/2012	20000	40000	20000	20000	2	Full Inv O/s
a2	INV-78	09/10/2012	15000	20000	5000	15000	3	Full Inv O/s
a2	INV-65	06/10/2012	3000	5000	2000	3000	6	Full Inv O/s
a2	INV-45	03/10/2012	10000	2000	-8000	2000	9	Partial Inv O/s
a2	INV-30	15/09/2012	20000	-8000	-28000	0	27

From this table I want to load a Final result table.

FinalTable:

custId	Inv. No.	Inv. Date	O/s Days	O/s Amount
a1	INV-80	10/10/2012	2	25000
a1	INV-75	09/10/2012	3	25000
a1	INV-50	05/10/2012	7	25000
a1	INV-47	04/10/2012	8	15000
a2	INV-79	10/10/2012	2	20000
a2	INV-78	09/10/2012	3	15000
a2	INV-65	06/10/2012	6	3000
a2	INV-45	03/10/2012	9	2000

I tryied previous in combanitation with peek and other soloutions given in community under the tag accumulate , running total, cummulative running toal

but I am fail. I can solve it on VB form or in VBA form but I want to solve it on QlikView Script.

So please help me to solve it.

Thanks in Advance

Regards

- Indian Qv Lover

[flipside · Accepted solution]

Hi indianqvlover,

This script should do most of it ..

custBalanceTable:

LOAD * INLINE [

custId,    custBal

a1,    90000

a2,    40000];

custTrnDetalsTable:

LOAD * INLINE [

custId,    InvNo,    InvDate,    InvAmount,

a1,    INV-80,    10/10/2012,    25000

a1,    INV-75,    09/10/2012,    25000

a1,    INV-50,    05/10/2012,    25000

a1,    INV-47,    04/10/2012,    35000

a1,    INV-44,    03/10/2012,    40000

a2,    INV-79,    10/10/2012,    20000

a2,    INV-78,    09/10/2012,    15000

a2,    INV-65,    06/10/2012,    3000

a2,    INV-45,    03/10/2012,    10000

a2,    INV-30,    15/09/2012,    20000];

// For each custID, create running total in reverse chronological order

For r = 1 to NoOfRows('custBalanceTable')

    intermediateTable:

    Load *, nummax(i_custBal,0) - nummax(i_Calculation,0) as [i_O/s Amt], today() - i_InvDate as [i_O/s Days];

    Load

        custId as i_custId,

        InvNo as i_InvNo,

        InvDate as i_InvDate,

        InvAmount as i_InvAmount,

        if(custId <> peek('i_custId'), FieldValue('custBal', $(r)), peek('i_Calculation')) as i_custBal,       

        if(custId <> peek('i_custId'), FieldValue('custBal', $(r)), peek('i_Calculation')) - InvAmount as i_Calculation

    resident custTrnDetalsTable

    where custId = FieldValue('custId', $(r))

    order by InvDate desc;

Next r;

flipside

[#3]

hi Flipside,

I am trying your solution. But I am in doubt about the performance because there is more then 1700000 records in details for mor then 8000 customers

Regards

-IndianQvLover

[#4]

Sir,

Solution given by you is working fine and it the same as I want but I am confuse, well after decleartion of intermediateTable you are loading *, and some other columns but from which table they are coming your are using 2 time load in the intermediateTable, Here I am confuse

Can you please explain this.

  intermediateTable:

    Load *, nummax(i_custBal,0) - nummax(i_Calculation,0) as [i_O/s Amt], today() - i_InvDate as [i_O/s Days];

    Load

        custId as i_custId,

        InvNo as i_InvNo,

Regards

- IndianQvLover

[#5]

hi IndianQvLover,

Solution provided by Flipside is correct as far as your doubt about performance on the larg data set is concern.

Answare of your doubt is comming from the your Intermediate Table. your can use 2 nested For-Next loops in Flipside's solutions and when your calculation column value is less then Zero then you can exit from inner For-Next loop

Hope it will help.

Regards

- Anil

[flipside]

Hi,

The first LOAD statement pulls data from the second LOAD statement (a bit like a sub query) - this just makes it easier to reference the calculated fields i_custBal and i_Calculation and use them in further calculated fields.  I included the * as all the fields in the sub query need loading into the table.

flipside

[#7]

hi IndianQvlover,

I am trying to explain Flipside's solution's tips & tricks step by step

1. Nested Load - If you are from RDBMS programming background then you can understand sub query / nested query like

select *, (calc1 * calc2) as calc4

     select *, (field4+field6) as calc1,(field5-field7) as calc2, ( (field8*field9)/field10 ) as calc3 from (

          select field1, field2, field3 ............field10 from table1

     ) as tab1

) as tab2

The Query returning total 14 columns - 10 column from inner most query, 3 column added on middle query, 1 column added on outer most query

Here Flipside is working on same approach

2. Peek - Syntax of peek is (FieldName, RowNo, Table Name) but when using without 2 other optional parameter then return / give value from previously read record.

3. nummax :: rangemax - Will give maximum value from the bunch of values rangemax(40,30,60,100) will return - 100, always use rangesum insted of nummax because nummax is obsolete now.

Flipside given a smart solution.

Hope my explanations will help if I explain properly.

Regards

- Anil

[#8]

hi, Flipside & anilsknp

Flipside sir thanks for solution and Anil sir thanks for nice explanation,

again thanks to both of you.

Regards

- IndianQvLover