Build a table only with changed values

[#1]

Hi all, let's say I have data like the following:

Code Received ShipMode

1 14/06/2010 Air

1 21/06/2010 Air

2 14/06/2010 Air

2 21/06/2010 Sea

Then in an object of QlikView I'd like to see only the code which changed the shipping mode (2 in this case). Is there a way to do that? Thanks a lot for your help.

[sicilianif]

You could do something like this in your load script:

a:
LOAD * INLINE [
Code,Received,ShipMode
1, 1/1/2010,Air
1,1/5/2010,Air
2,1/1/2010,Air
2,1/5/2010,Sea
];

Left join (a)
Load Code,
Received,
ShipMode,
If(Peek(Code)=Code,If(Peek(ShipMode)<>ShipMode,1,0),0) as Changed
resident a
order by Code, Received

Then just display anything with Changed=1

Frank

[#3]

Hi Frank, thanks for your quick reply, but I haven't understood what you suggest. I already loaded a table containing the data I showed: should I add another inline table? And in addition it wouldn't be so simple because in the table there are thousands of values (in it every Code has 2 ShipMode which can be different or not).

[sicilianif]

Sorry, the inline table was just for example.

You would just be adding something like this to your code after the load of your table:

Left join (CurrentTable)
Load Code,
Received,
ShipMode,
If(Peek(Code)=Code,If(Peek(ShipMode)<>ShipMode,1,0),0) as Changed
resident CurrentTable
order by Code, Received

What this is doing is appending a Changed column to your table through a left join. The if statement is using the Peek function to check the previous "Code" to make sure they are the same, and if they are it checks the ship mode. If the ship mode is different, then it puts a 1 in the Changed column.

The end result for the data you provided would be something this:

Code Received ShipMode Changed
1 14/06/2010 Air 0
1 21/06/2010 Air 0
2 14/06/2010 Air 0
2 21/06/2010 Sea 1

[#5]

Thanks! You are a genius!

Unfortunately in my real source table for every code you can find more than two lines (that is there are more than two dates in Received). Do you know a way to make the comparison selectively? For example for Code=1 I could have a third row like the following:

1 29/06/2010 Sea

Then I'd like to be able to do a comparison choosing for example only 14/06/2010 and 29/06/2010. I don't need to it dinamically, I mean that I could have various additional field "Changed", I'll do one additional "Changed" for every combinations which can be done.

[sicilianif]

In the case of the addition line, it would also have Changed=1.

Can you give me an example of what the user needs for output?

[#7]

Ok, what I have is the table like the one we spoke about. The user needs to know which codes changed their ShipMode for example comparing 14/06/2010 and 29/06/2010 (he can change the dates to be compared). Does it make sense? Thanks again for your help, you are kind!

[sicilianif · Accepted solution]

Attached is a sample using 2 input fields for dates. It should work as long as there is only one record per code per date.

Also, with this method, you would not need to add the code that I gave you before.

[johnw]

Another way would be to select two (or more) dates from the Received list box, and then use this as a calculated dimension for whatever chart is only supposed to show the Codes where the ship mode has changed:

aggr(if(count(distinct ShipMode)>1,Code),Code)

[#10]

That's the solution! Thanks!

I'll only change the input fields in ListBoxes. Thanks again!