Add extra line during load???

Mon, 09 Jan 2012 22:22:08 GMT

Hello,

I have a table:

Header 1	Header 2	Header 3	Header 4
Material	Leve2	Source1	Source2
AA	1
AA	2		2
AA	3		2
AA	4	2
AA	5	2
BB	1
BB	2		2
BB	3		2
BB	4	2
BB	5	2

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:

LOAD Material,

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

(ooxml, embedded labels, table is Sheet1);

left join(T1)

Load Material,

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:

Header 1	Header 2	Header 3	Header 4	Header 5
Material	Level2	Source1	Source2	Date
AA	1			2011-01-01
AA	2		2	2011-01-01
AA	2		3	2011-01-05
AA	3		2	2011-01-01
AA	4	2		2011-01-01
AA	5	2		2011-01-01
BB	1			2011-01-01
BB	2		2	2011-01-01
BB	3		2	2011-01-01
BB	4	2		2011-01-01
BB	5	2		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?

Add extra line during load???

pover — Tue, 10 Jan 2012 00:47:34 GMT

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

topic Add extra line during load??? in QlikView

Add extra line during load???

Add extra line during load???