topic Previous Dimension Value In Straight Table in QlikView

Previous Dimension Value In Straight Table

joshabbott — Wed, 02 May 2012 12:08:22 GMT

Hello,

I have a straight table that has data like:

Division Sales

A 100

null 200

null 50

B 44

null 33

What I'm looking for is a way in the straight table to get the previous row's dimension value. For instance, row 1 above has an A for division, but rows 2 and 3 are null, I would like both of them to be A. Row 4 has a B value but row 5 is null, I would like this to be B.

Could someone help me? I'm just give a very small sample set of this data, I understand you can use a previous in the script, but I would prefer not to go that direction as it would create millions of extra rows in my situation. Thank you for any help!

Josh

Previous Dimension Value In Straight Table

Wed, 02 May 2012 16:22:28 GMT

You can accomplish this using the peek function, which pulls up the previous entries value in the table.

For the script you would have:

Temp:

LOAD * Inline [

Division, Sales

A, 100

null, 200

null, 50

B, 44

null, 33

];

Temp1:

LOAD Sales,

IF((Division='null'),peek(Division),Division) as Division,

' ' as Junk

Resident Temp;

drop table Temp;

Only chances are you would have an actual null value in Division rather than the string null so your IF statement would go like this:

IF(isNull(Division),peek(Division),Division) as Division,

Hope this helps!

-Brandon

Previous Dimension Value In Straight Table

joshabbott — Wed, 02 May 2012 16:35:16 GMT

My goal was to do this in a dimension, instead of loading millions of records into the QlikView DB. I was unable to come up with a way to do this in the dimension.

What I ended up doing was something similar to what Brandon had in the post above. I brought in all 4+ million records using outer join (yuck), then in my script sorted this how it needed to be sorted.

I made another pass, and brought back my division field as:

If(Division > '', Division, PEEK('Division', RowNo() - 2)) as Division

I originally thought I could use the 'Previous' function in my script, but this only got the previous record from my originating data source, and not the rows as they were created. Peek seemed to take care of this.