Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

hansdevr
Contributor II

Include new field with subtotals/status in load script

Hi Everyone,

I have a table, which includes Claim#, Date of payment and amount payed.

I would like to add a field, which adds up all the payments for a certain claim, and puts the results as an "Amount payed upto now" in a separate field, which I would like to call "Sub". See example below. How would I go about this in the load script?

Any help is very welcome!

Hans

   

Claim#Payment dateAmountSub
100011-6-2004€ 40,00€ 40,00
1000315-4-2016€ 100,00€ 900,00
1000310-4-2016€ 600,00€ 800,00
100031-4-2016€ 200,00€ 200,00
1000611-5-2016€ 500,00€ 10.500,00
100061-5-2016€ 10.000,00€ 10.000,00
1 Solution

Accepted Solutions

Re: Include new field with subtotals/status in load script

Try this:

Table:

LOAD * INLINE [

    Claim#, Payment date, Amount

    10001, 1-6-2004, 40

    10003, 15-4-2016, 100

    10003, 10-4-2016, 600

    10003, 1-4-2016, 200

    10006, 11-5-2016, 500

    10006, 1-5-2016, 10000

];

FinalTable:

LOAD *,

  If(Claim# = Previous(Claim#), RangeSum(Amount, Peek('Sub')), Amount) as Sub

Resident Table

Order By Claim#, [Payment date];

DROP Table Table;


Capture.PNG

14 Replies

Re: Include new field with subtotals/status in load script

May be like this:

Table:

LOAD Claim#,

          [Payment date],

          Amount

FROM Source;

Left Join (Table)

LOAD Claim#,

          Sum(Amount) as Sub

Resident Table

Group By Claim#;

hansdevr
Contributor II

Re: Include new field with subtotals/status in load script

Hi Sunny, fast as lightning you are!!

I may be wrong, but does this take the order of dates in account? Should I add order by Claim#, Date ASC

Re: Include new field with subtotals/status in load script

I think the above is not going to give what you are looking for. Working on an alternate solution

Re: Include new field with subtotals/status in load script

try this

Table:

LOAD Claim#,

          [Payment date],

          Amount

FROM Source;


New:

noconcatenate

LOAD *,

          if( Claim# =previous(Claim#), Rangesum(Amount,peek('Sub')),Amount) as Sub

Resident Table

order by Claim#,[Payment date] ;


drop table Table;

Re: Include new field with subtotals/status in load script

Try this:

Table:

LOAD * INLINE [

    Claim#, Payment date, Amount

    10001, 1-6-2004, 40

    10003, 15-4-2016, 100

    10003, 10-4-2016, 600

    10003, 1-4-2016, 200

    10006, 11-5-2016, 500

    10006, 1-5-2016, 10000

];

FinalTable:

LOAD *,

  If(Claim# = Previous(Claim#), RangeSum(Amount, Peek('Sub')), Amount) as Sub

Resident Table

Order By Claim#, [Payment date];

DROP Table Table;


Capture.PNG

hansdevr
Contributor II

Re: Include new field with subtotals/status in load script

Hahahaha, Sunny and Kushal have the exact same solutions!!

How do I mark BOTH answers as being correct?!?

Thnx, guys, you've both been a great help!!

Re: Include new field with subtotals/status in load script

Again I think there will be fight for correct answer mark

Re: Include new field with subtotals/status in load script

Please check the reply time.. who replied first mark that as correct

Re: Include new field with subtotals/status in load script

Tu le le bhai correct answer . Waise the reply time changes based on modifications made to the posts. I added the sample after my intial response

Community Browser