1 Reply Latest reply: Jan 9, 2012 7:47 PM by Karl Pover

Hello,

I have a table:

MaterialLeve2Source1Source2
AA1

AA2
2
AA3
2
AA42
AA52
BB1

BB2
2
BB3
2
BB42
BB52

and Material is in Parent-Child structure. If Level2 is 1 then it is Parent material and if it is >1 it is child material.  Total cost for parent material is sum(cost for child materials). I have two sources and if it is empty in source 1 cost is in source2. Att the end, I need cumulative column that summarize all cost. The problem is solved like this:

T1:

Level2,

Source1,

Source2,

IF(Level2=1,

if(isnull(Source1)<>-1,Source1,if(isnull(Source2)<>-1,Source2,0)),

if(isnull(Source1)<>-1,Source1,if(isnull(Source2)<>-1,Source2))) as Result

FROM

[C:\...\Desktop\Table1.xlsx]

(ooxml, embedded labels, table is Sheet1);

left join(T1)

sum(Result) as Summa

Resident T1

Group By Material;

With this, I can make another table with only two lines:

AA =>Summa is 8

BB =>Summa is 8

Summa is sum(All child costs)

Now, the problem is when the cost is changed for child material. If, for example for material AA level2 is 2 cost is changed to 3, table will look like this:

MaterialLevel2Source1Source2Date
AA1

2011-01-01
AA2
22011-01-01
AA2
32011-01-05
AA3
22011-01-01
AA42
2011-01-01
AA52
2011-01-01
BB1

2011-01-01
BB2
22011-01-01
BB3
22011-01-01
BB42
2011-01-01
BB52
2011-01-01

New line is inserted, and my code does not work. In this situation, I would like to have two Parent AA lines with Result which is sum(All child costs) but in first case Level2 will be 2 (date 2011-01-01) and in second case Level2 will be 3 (date 2011-01-05). Final result should be:

AA => Summa is (8) 2011-01-01

AA => Summa is (9) 2011-01-05

BB => Summa is (8) 2011-01-01

Well, this looks like that I need to add one extra (parent) line during load (or copy all child twice and sumarize two times), and I have no idea how. Any suggestions?

If you have a table that contains the parent-child relationships then you might find things are easier in QlikView using the hierarchy() and hierarchybelongsto() functions.

On another point if you want to see how costs have evolved over time, you can either save of the last version and create new lines for a second version which I think is what you are planning to do, or you can add a line that adds to or substracts from the previous values.  For example, in inventory applications there is usually a table that each day records the inventory on stock at the end of each day and another table that records the incoming and outgoing stock.

Karl