Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

How to transform a table

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

1 Solution

Accepted Solutions
MVP
MVP

Re: How to transform a table

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

2 Replies
MVP
MVP

Re: How to transform a table

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

Not applicable

Re: How to transform a table

Really Thanks !!! It works

Stefan

Community Browser