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

    Add extra line during load???

      Hello,

       

      I have a table:

      Header 1Header 2Header 3Header 4
      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:

      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

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

      (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 1Header 2Header 3Header 4Header 5
      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?

        • Add extra line during load???
          Karl Pover

          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