topic Re: How To Solve This Scenario On Script in QlikView

How To Solve This Scenario On Script

Fri, 12 Oct 2012 09:46:50 GMT

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

Re: How To Solve This Scenario On Script

flipside — Fri, 12 Oct 2012 11:04:18 GMT

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

Re: How To Solve This Scenario On Script

Fri, 12 Oct 2012 11:14:12 GMT

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

Re: How To Solve This Scenario On Script

Fri, 12 Oct 2012 11:42:08 GMT

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

Re: How To Solve This Scenario On Script

Fri, 12 Oct 2012 11:51:49 GMT

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

Re: How To Solve This Scenario On Script

flipside — Fri, 12 Oct 2012 12:25:36 GMT

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

Re: How To Solve This Scenario On Script

Fri, 12 Oct 2012 13:26:10 GMT

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

Re: How To Solve This Scenario On Script

Fri, 12 Oct 2012 13:47:50 GMT

hi, Flipside & anilsknp

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

again thanks to both of you.

Regards

- IndianQvLover