topic Re: How to transform a table in QlikView

How to transform a table

Sat, 28 Jul 2012 13:02:35 GMT

Dear all,

I'm newer and I'm facing the the following problem. I've a table like this:

Supplier Year Month Paid amount

A 2012 01 1000

A 2012 02 1500

A 2012 03 800

B 2012 01 5000

B 2012 02 2000

B 2012 03 1500

B 2012 04 1000

where each line, represents the paid amount in the month (per year and supplier)

I've to build a new table like this:

Supplier Year Month Total paid amount at the month

A 2012 01 1000

A 2012 02 2500 (1000 + 1500)

A 2012 03 3300 (1000 + 1500 + 800)

B 2012 01 5000

B 2012 02 7000 (5000 + 2000)

B 2012 03 8500 (5000 + 2000 + 1500)

B 2012 04 9500 (5000 + 2000 + 1500 + 1000)

where each line, contains the progressive paid amount. For example: for the supplier A, the second line contains the sum of January (01) and February(02),

1000 + 1500.

The third line, is the contains the sum of 1000 + 1500 + 800 and so on

I hope somebody can help me

Thank in advance

Re: How to transform a table

swuehl — Sat, 28 Jul 2012 13:15:26 GMT

Hi peronist,

you should ensure that your table is ordered appropriately, then you can use peek() function to retrieve the value of a field in a previous record (i.e. this allows you to "add up" values from to different record lines):

INPUT:

LOAD * INLINE [

Supplier, Year, Month, Paid amount

A, 2012, 01, 1000

A, 2012, 02, 1500

A, 2012, 03, 800

B, 2012, 01, 5000

B, 2012, 02, 2000

B, 2012, 03, 1500

B, 2012, 04, 1000

] ;

FACT:

LOAD *,

rangesum([Paid amount],if(Supplier=peek(Supplier),peek(Total))) as Total

Resident INPUT order by Supplier, Month;

drop table INPUT;

If you have multiple years, you may want to order by Supplier, Year, Month and also include a check for Year in the if() statement:

rangesum([Paid amount],if(Supplier=peek(Supplier) and Year=peek(Year),peek(Total))) as Total

Hope this helps,

Stefan

Re: How to transform a table

Mon, 30 Jul 2012 14:05:26 GMT

Really Thanks !!! It works

Stefan