5 Replies Latest reply: Apr 15, 2017 9:20 AM by Andy Weir RSS

    LOAD

    mani ram

      Hi Experts,

       

      I have a excel file like this

       

       

      Operation CodeOperation DescriptionSantroGetzAccentElantra
      No.TaxAmtTot Amt(tax+amt)No.TaxAmtTot Amt(tax+amt)No.TaxAmtTot Amt(tax+amt)No.TaxAmtTot Amt(tax+amt)

      While i am loading i am getting like this,

      LOAD

           [Operation Code],

           [Operation Description],

           Santro,

           F4,

           F5,

           F6,

           Getz,

           F8,

           F9,

           F10,

           Accent,

           F12,

           F13,

           F14,

       

      But i want like this .

      we have any chance to do this in qlikview. like Below mention way.

      LOAD

           [Operation Code],

           [Operation Description],

           Santro no,

           Santro tax,

           Santro amt,

           Santro Tot amt,

        • Re: LOAD
          omar bensalem

          can you share your excel file?

          • Re: LOAD
            Andy Weir

            Looks like you need to load your header from row 2 you will then load something like this.

             

            LOAD

                 F1 as "Operation Code",

                 F2 as "Operation Description",

                 'Santro' as "Operation Type",

                 no,

                 tax,

                 amt

             

            Exclude the total amout as you can write a simple measure to derive this from amt + tax

             

            Repeat the load for Getz and Accent by concatenating the loads into one fact table and you'll have a good data model to play with in Qlik.

              • Re: LOAD
                mani ram

                Hi Experts,

                 

                Please find the attached file.

                 

                 

                regards

                  • Re: LOAD
                    omar bensalem

                    Hi mani,

                     

                    Try as follow:

                    load your file directly without changing a thing, then change all the load statement as follow:

                     

                     

                    LOAD

                        F1 as "Operation Cod",

                        F2 as "Operation Description",

                        No. as "Santro No",

                        Tax as "Santro Tax",

                        Amt as "Santro amt",

                        "Tot Amt(tax+amt)",

                        No.1 as Getz,

                        Tax1,

                        Amt1,

                        "Tot Amt(tax+amt)1",

                        No.2 as Accent,

                        Tax2,

                        Amt2,

                        "Tot Amt(tax+amt)2",

                        No.3 as Elantra,

                        Tax3,

                        Amt3,

                        "Tot Amt(tax+amt)3",

                        No.4 as "NF-Sonata",

                        Tax4,

                        Amt4,

                        "Tot Amt(tax+amt)4",

                        No.5 as "EF-Sonata",

                        Tax5,

                        Amt5,

                        "Tot Amt(tax+amt)5",

                        No.6 as Tucsan,

                        Tax6,

                        Amt6,

                        "Tot Amt(tax+amt)6",

                        No.7 as Terracam,

                        Tax7,

                        Amt7,

                        "Tot Amt(tax+amt)7",

                        No.8 as i10,

                        Tax8,

                        Amt8,

                        "Tot Amt(tax+amt)8",

                        "No" as i20,

                        Amt9,

                        Tax9,

                        "Tot Amt(tax+amt)9",

                        No.9 as Verna,

                        Tax10,

                        Amt10,

                        "Tot Amt(tax+amt)10",

                        No.10 as Other,

                        Tax11,

                        Amt11,

                        "Tot Amt(tax+amt)11",

                        No.11,

                        Tax12,

                        Amt12,

                        "Tot Amt(tax+amt)12" as "TOTAL"

                    FROM [lib://source/S.A WISE.xls]

                    (biff, embedded labels, header is 1 lines, table is Rajiv$);

                     

                    Don't forget to make header is 1 lines like in the line above !

                    • Re: LOAD
                      Andy Weir

                      Hi Mani

                       

                      Please find attached your load with abit more work done to it...

                       

                      By loading your data this way you can use the power of Qlik much more easily in the front end.

                       

                      Ive added a pivot table to show you your data back as you presented it but now you can start cutting and anayzing your data alot easier.  I've also created a measure for your total amount (Amt + tax).  you should load up values that can be derived from others unless that will put alot of load onto the front end use of the dashboard.

                       

                      This should get you started.  Please mark any replies as either helpful/Correct.

                       

                      Regards

                       

                       

                      Andy