Compare individual datasets within monthly data snapshots, find changed rows and fields, group and report

[#1]

Dear all,

I am currently evaluating QlikView and whether it would help me during my day-to-day business. At the moment I am intensively trying out the "Personal Edition".

I have a question, whether and how QlikView would be able to solve the following problem:
Each month I get a file of some 5000 rows, being a snapshot of data on all our stock-items. The Layout of this file is always identical.

Example Month of June:

month	id	name	stock	room	corridor	rack	latest_check	status	est.value	...
2010-06	4711	Testproduct1	Berlin-West	13	5	15	2010-04-15	good	100 EUR	...
2010-06	0815	Testproduct2	Munich-East	3	75	8	2010-02-11	ok	50 EUR	...
...	...	...	...	...	...	...	...	...	...	...

Example Month of July:

month	id	pname	stock	room	corridor	rack	latest_check	status	est.value	...
2010-07	4711	Testproduct1	Berlin-West	10	5	15	2010-04-15	miserable	10 EUR	...
2010-07	0815	Testproduct2	Munich-East	3	75	8	2010-02-11	ok	50 EUR	...
...	...	...	...	...	...	...	...	...	...	..

At the moment we always have to use Excel Pivots to compare the datasets manually and try to find out the differences:

For product 4711 you would see that the room has changed, alongside with the status and estimated_value.
For product 0815 you would see that nothing has changed.
...

We then usually go into our datacollection and sort them into groups (new items, disappeared items, change of value, change of stock-location, change of status, etc.).
The reports we are building with an Excel Pivot, groupwise showing the amount of changed data-sets with a possibility to drill into the individual data-set - the rest (unchanged sets) are not interesting.

What I would like to understand is the following:

• How could Qlikview automate this process of grouping items whether information changed compared to the previous month?
• Could Qlikview even calculate that dynamically, after a selection of two (three, four, unlimited) months? (Showing only the Delta while explaining what exactly changed?)
• Could this information be shown within a single chart, e.g. a waterfall- or a boxchart? (With e.g. 6-xxx Boxes, aggregating the number of: 1. change of value items, 2. change of stock-location-items, 3. change of status-items, 4. change of XXX- items, 5. new items, 6. disappeared items, etc.)
• Could a drill-in functionality be built? (A click on the box shows you the list of items which fall into this category)
• Can this problem be solved within the Personal Edition, so that I can try it out myself?

I would be very thankful for an answer.

Thank you for your support

KR
Lumihei

[#2]

Would "set analysis" help to solve this problem?

[johnw]

Well, this probably isn't exactly what you need, but here's an example of highlighting all of the differences between order items from two different source tables. In your case, replace the concept of "source table" with "monthly snapshot" and I think you get something very similar. As I set it up, if nothing has changed, the row won't even be loaded. If the row is in one table but not the other, the key is highlighted in gray. If the row is in both tables, but some field values are different, then the differences are highlighted in gray. Might be a reasonable starting point, even if it's not exactly what you're asking for.

I can explain in more detail HOW it's doing what it's doing if it looks useful, and if you can't figure it out.

Oh, wait. You can't open the file in personal edition. OK. First, the result looks like this:

Here's the script:

Table:
LOAD *
,Customer & ';' & Amount as Data
,'Table 1' as Table
INLINE [
OrderItem, Customer, Amount
OI1, Ann, 500
OI2, Bob, 600
OI3, Charlie, 2000
OI4, Dennis, 200
OI6, Fred, 500
];
CONCATENATE
LOAD *
,Customer & ';' & Amount as Data
,'Table 2' as Table
INLINE [
OrderItem, Customer, Amount
OI2, Bobby, 600
OI3, Charlie, 200
OI4, Dennis, 200
OI5, Erin, 500
OI6, Fred, 500
];
INNER JOIN (Table)
LOAD OrderItem
WHERE Different
;
LOAD OrderItem, count(OrderItem)<2 or maxstring(Data)<>minstring(Data) as Different
RESIDENT Table
GROUP BY OrderItem
;
DROP FIELD Data
;

Then create a straight table. Your dimensions are OrderItem, Table, Customer and Amount. Your expression is 1. On the presentation tabl, select the 1 column, and select "hide column". Back on the dimensions tab, there is a + next to each dimension. Click on it, and it will give you some additional settings. Select "background color" for each dimension, and enter these expressions:

For OrderItem, enter =if(count(total <OrderItem> OrderItem)=1,lightgray())
For Table, enter =if(count(total <OrderItem> OrderItem)=1,lightgray())
For Customer, enter =if(count(total <OrderItem> distinct Customer)=2,lightgray())
For Amount, enter =if(count(total <OrderItem> distinct Amount)=2,lightgray())

I think that's all the magic, but if it doesn't work, I've probably missed something.

[#4]

But this solution doesn't scale right? If he for instance selected three monthly snapshots he would have to add another sourcetable to the script, right.

I'm asking, because I'm currently trying to solve exactly the same problem.

[johnw]

weixi wrote:But this solution doesn't scale right? If he for instance selected three monthly snapshots he would have to add another sourcetable to the script, right.

It looks like the only part that doesn't scale up is how I handled the "Different" field, which is only being used to remove keys (order items) with no differences across tables (months). For what we're now proposing, there's no need to remove any keys, so we don't need the "Different" field in the first place.

The rest of it appears to scale up just fine, at least with minor tweaks to not hard code the number of tables (months) as 2. Attached is an example for three months that seems to be working, and it looks like you could add as many months as you wanted.

Script:

Table:
LOAD *
,Customer & ';' & Amount as Data
INLINE [
Month, OrderItem, Customer, Amount
1, OI1, Ann, 500
1, OI2, Bob, 600
1, OI3, Charlie, 2000
1, OI4, Dennis, 200
1, OI6, Fred, 500
2, OI2, Bobby, 600
2, OI3, Charlie, 200
2, OI4, Dennis, 200
2, OI5, Erin, 500
2, OI6, Fred, 500
3, OI1, Ann, 500
3, OI2, Bob, 600
3, OI3, Charlie, 2000
3, OI4, Dennis, 2000
3, OI5, Erin Smith, 500
3, OI6, Fred, 498
3, OI7, Fred, 500
];

Chart background color expressions:

For OrderItem, =if(count(total <OrderItem> OrderItem)<count(total distinct Month),lightgray())
For Month, =if(count(total <OrderItem> OrderItem)<count(total distinct Month),lightgray())
For Customer, =if(count(total <OrderItem> distinct Customer)>1,lightgray())
For Amount, =if(count(total <OrderItem> distinct Amount)>1,lightgray())