10 Replies Latest reply: Jul 13, 2015 5:06 AM by phuviet pham RSS

    Split record during load by distribution key

      Hello all,

       

      I have a new question. We want to compare our costs versus our income.

      Tthe company has several plants. And some costs can't be addresses to one plant.

      So they created a distribution key that counts for all costs that can't be addresses to one specific plant.

      for example:

      We have a cost of €1000.

           plant 1 gets 50% = €500

           plant 2 gets 20% = €200

           plant 3 gets 30% = €300

       

      Can I split this up during the load of Qlikview?

      In stead of having 1 record of €1000, I want three records.

       

      thank you for your help.

       

      Stijn

        • Re: Split record during load by distribution key
          Manish Kachhia

          Cost * 0.5 as Plant1Cost,

          Cost * 0.2 as Plant2Cost

          Cost * 0.3 as Plant3Cost

            • Re: Split record during load by distribution key

              Hi

              I have the same Answear as Manish.

               

              Create 3 Calculations

              • Re: Split record during load by distribution key

                Hello,

                 

                thank you for the quick reply.

                Doesn't this mean I get 1 record with a field for every plant?

                I need to combine these lines with lines that can be assigned to a plant.

                So every records has a plant and a cost. (Other things as well off course)

                My result of these lines need to look the same. Every records has 1 plant and 1 cost.

                 

                kind regards

                stijn

                  • Re: Split record during load by distribution key
                    Manish Kachhia

                    Provide 5 lines of sample data...

                      • Re: Split record during load by distribution key

                        i think you should paint what you want exactly. Its better to think how to do it

                        • Re: Split record during load by distribution key

                          I haven't gotten exact data yet but:

                          Data to load

                          DISTRIBUTION KEY:

                          PLANT          KEY

                          000               50

                          001               30

                          002               20

                           

                          DIRECT data

                          FIRM     PLANT     NUMBER     DATE          COST

                          1           000          123              20150713    100

                          1           001          456              20150712    200

                          1           002          789              20150711    300

                          1           000          159              20150711    500

                           

                          INDIRECT data

                          FIRM     PLANT    NUMBER      DATE          COST

                          1                         357               20510709     2000

                           

                           

                          RESULT

                          FIRM     PLANT    NUMBER      DATE          COST

                          1           000          123              20150713    100

                          1           001          456              20150712    200

                          1           002          789              20150711    300

                          1           000          159              20150711    500

                          1           000          357              20150709    1000

                          1           001          357              20150709    600

                          1           002          357              20150709    400

                            • Re: Split record during load by distribution key
                              Manish Kachhia
                              Plant:
                              Load PLANT as PlantNumber, KEY/100 as KEY Inline
                              [
                                PLANT, KEY
                                000, 50
                                001,    30
                                002,    20
                              ];
                              
                              
                              DIRECT:
                              Load * Inline
                              [
                                FIRM, PLANT, NUMBER, DATE,          COST
                                1,    000,   123,    20150713,    100
                                1,    001,   456,    20150712,    200
                                1,    002,   789,    20150711,    300
                                1,    000,   159,    20150711,    500
                              ];
                              
                              
                              NoConcatenate
                              INDIRECT:
                              Load * Inline
                              [
                                FIRM,     PLANT,    NUMBER,      DATE,          COST
                                1,        ,         357,          20510709,     2000
                              ];
                              
                              
                              Let vPLANT = FieldValueCount('PlantNumber');
                              
                              
                              For i= 0 to vPLANT-1
                              Let vPlantName = Peek('PlantNumber',$(i),'Plant');
                              Let vKey = Peek('KEY',$(i),'Plant');
                                NoConcatenate
                                NEWTABLE:
                                Load 
                                FIRM,
                                '$(vPlantName)' as PLANT,
                                NUMBER,
                                DATE,
                                COST * $(vKey) as COST
                                Resident INDIRECT;
                              Next i
                              
                              
                              Drop Table INDIRECT;
                              
                              
                              Concatenate (DIRECT) Load * Resident NEWTABLE;
                              
                              
                              Drop Table NEWTABLE;