How to combine two rows of the same table and display it as old and new value in a single row

[#1]

I'm bit of stumped in the syntax of qlikview to achieve this. I think I can work if it was an SQL statement but don't know how to do it in qlikivew. Here's my problem:

1. I have a table, say MainTable, which as a linear data:

MainTable:

Load * Inline

[

PartNum, Price, VendorNum, MM, YY

aa, 20, Vaa2, 3, 2013

aa, 30, Vaa3, 5, 2013

aa, 10, Vaa1, 1, 2013

bb, 10, Vbb1, 1, 2013

cc, 20, Vcc1, 1, 2013

bb, 15, Vbb2, 2, 2013

bb, 10, Vbb2, 5, 2013

cc, 15, Vcc2, 4, 2013];

What I want to achieve from this table is something like this (chronologically ordered price changes):

PartNum, OldPrice, NewPrice, OldVendorNum, NewVendorNum, OldMonth, NewMonth, OldYY, New YY

aa, 10, 20, Vaa1, Vaa2, 1, 3, 2013, 2013

aa, 20, 30, Vaa2, Vaa3, 3, 5, 2013, 2013

bb, 10, 15, Vbb1, Vbb2, 1, 2, 2013, 2013

bb, 15, 10, Vbb2, Vbb2, 2, 5, 2013, 2013

cc, 20, 15, Vcc1, Vcc2, 2, 5, 2013, 2013

I was thinking of doing something like this:

NeededTable:

NoConcatenate

Load

PartNum, Price as OldPrice, VendorNum as OLDVendorNum, MM as OLDMM, YY  as OLDYY

Resident MainTable

Left Join (NeededTable)

Load Top 1

PartNum, Price as NewPrice, VendorNum as NewVendorNum, MM as NewMM, YY as NewYY

Resident MainTable

Where

OLDPrice<>Price AND OldMM<MM AND OLDYY<= YY

;

But I don't know how to do Top 1 in qlikview.

Please advise on how to achieve the resulting table. Thank you in advance.

[Anonymous]

Samir

Have a look at the PEEK function, which lets you look at rows already loaded.

QlikView explains its parameters and how to use it.

Best Regards,     Bill

[preminqlik]

Hi please find attachment,

Hope this help u

Regards

Premhas

[#4]

Thanks for the quick responses. But I see one problem with your solution: the previous statement is grabbing the previous row without the consideration of the part number, however. I guess I can use if statement to check for part number. But I think I can use your solution as a template to do mine. If you have any other suggestions, please let me know.

Thanks again.

Samir

[preminqlik]

Hey samir hi again,

i too noticed  , once load full data and let me know , mean while i'll do R&D on it and i'l let u know..

Regards

Premhas

[CELAMBARASAN]

Hi,

You can add a if statement to it

If(Previous(PartNumber) <> PartNumber, Vendor, Previous(Vendor))

[#7]

Sorry for the late response. I got side track on this.

Celambarasan, I had already tried using the if statement but does not seem to work. Thank you for you response.

I'm still trying to figure this out.

[#8]

The if statement did not work either. Have you had any luck on this problem? Thanks in advance.

[Clever_Anjos]

You don´t need to do it a script level.

You can do this using a straight table

Dimension: PartNum

Expression for OldPrice : firstsortedvalue(Price,makedate(YY,MM))

Expression for NewPrice : firstsortedvalue(Price,-makedate(YY,MM))

Other expressions are similar

[Anonymous · Accepted solution]

Samir,

You'll want to do something like this, where you load the table in a particular order then check the previous record for a repeated value.  QlikView is much different than SQL in the sense that QV always loads things in a sequence, much like if you were to always use cursors in SQL.

NeededTable:
NoConcatenate
LOAD
  PartNum,
  Price as NewPrice,
  VendorNum as NewVendorNum,
  MM as NewMonth,
  YY as NewYY,
  If(
  PartNum = Previous(PartNum),
  Peek(NewPrice)
  ) as OldPrice,
  If(
  PartNum = Previous(PartNum),
  Peek(NewVendorNum)
  ) as OldVendorNum,
  If(
  PartNum = Previous(PartNum),
  Peek(NewMonth)
  ) as OldMonth,
  If(
  PartNum = Previous(PartNum),
  Peek(NewYY)
  ) as OldYY
Resident
  MainTable
Order by
  PartNum,
  YY,
  MM
;

What this statement is doing is loading a row, checking to see if the PartNum of this row is the same as the previous row, then providing values to the "Old" fields if the current and previous PartNums match.

One thing to look out for is that this statement will generate nulls in the "Old" fields for each PartNum's first row.  There are ways around this, most common is to just load the table again with a "Where" clause excluding rows when a particular field is null.