Previous Dimension Value In Straight Table

[joshabbott]

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

[#2]

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

[joshabbott]

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.