10 Replies Latest reply: Mar 6, 2018 9:29 AM by youssef belloum RSS

    How to make Single QVD?

    Siva Boggarapu

      Hi All,

       

      Please look at the document and I have two sheets 3 months and 12 months sheet.

       

      I need combine data columns in 3 months sheet and 12 months sheets.

       

      Columns starts from 3 months sheet  ShoID, Repair Facility....

      Columns starts from 12 months sheet ShoID, Repair Facility....


      I want to combine them into one single QVD. There is no time stamp both sheets. How should I combine them into one QVD?


      Thaks,

      Siva

        • Re: How to make Single QVD?
          Niclas Anderström

          Hi,

           

          You need to load the sheets separately, but you can easily concatenate the sheets on top of each other after/while loading. In order to be able to separate what data comes from what sheet you can add a '3 Months' as SheetID in your load script.

           

          Then store the final table as a qvd.

          • Re: How to make Single QVD?
            youssef belloum

            Hi,

             

            your file needs transformations,

             

            first you should remove all the Merged cells, you should have only clean columns and rows.

             

            after, the logic is if your 3 month sheet and your 12 months sheet have the same columns, the same fields, you can simply concatenante them.

             

            like this:

             

            3_months_sheet:

            load

            .

            .

            .

             

            from 3_months_sheet;

             

            concatenate

             

            12_months_sheet:

            load

            .

            .

            .

            from 12_months_sheet;

             

            ==> like this you will have only one logical table that contains 2 differents table (3 months, 12 months)

              • Re: How to make Single QVD?
                youssef belloum

                I forgot, as Nicolas said, don't forget to Flag your tables using a new field, like this:

                 

                '3_monhts' as Table_type //when you load your 3 months table

                 

                '12_monhts' as Table_type //when you load your 12 months table

                  • Re: How to make Single QVD?
                    Siva Boggarapu

                    Hi Youssef,

                     

                    Thanks for giving logic. But from the excel sheet while I am loading data some fields are missing.

                     

                    How Can I add all the fields without missing all?

                     

                    Thanks,

                    Siva

                      • Re: How to make Single QVD?
                        Bram Knuever

                        You can simply concatenate them on each other with the flag mentioned above. Any field not available in the first table will be added with the concatenate.

                          • Re: How to make Single QVD?
                            Siva Boggarapu

                            Hi Bram Knuever,

                             

                            When I make load

                             

                            LOAD

                                "Shop ID",

                                "Repair Facility",

                                "Market - State - City - Zipcode",

                                Cap.,

                                "# of

                            Ests",

                                "Repairs/

                            Capacity",

                                "0-1000",

                                "% Rank",

                                "% WBNP",

                                F10,

                                "WBNP

                            Freq.",

                                F12,

                                "Customer

                            Concerns",

                                F14,

                                F15,

                                "% Repair

                            Concerns",

                                F17,

                                "# of

                            Audits",

                                "Photo

                            Quality",

                                "Avg

                            Rental",

                                F21,

                                "Rental

                            Count",

                                "Drop to

                            Pick-up",

                                "Start to

                            Finish",

                                "Hours /

                            Day",

                                "Avg

                            Supp $",

                                "% of

                            Orig",

                                "FB Net

                            Supp",

                                "% Estimate Difference",

                                F30,

                                "# of

                            Insp.",

                                "Total

                            Est $",

                                "Average

                            Gross $",

                                "Average

                            Part $",

                                "OEM %

                            Part $",

                                "Recy.  %

                            Part $",

                                "Non-OE

                            Part $",

                                "Average

                            Labor $",

                                "Repr. vs.

                            Repl. $",

                                "Paint

                            Mtls $"

                               

                            FROM [lib://Qlik/Single QVD.xlsm]

                            (ooxml, embedded labels, header is 12 lines, table is [3 month]);.

                             

                             

                            Capacity",         

                            Concerns",

                                F14,

                                F15,

                                "% Repair

                             

                                "0-1000",

                                "% Rank",

                                "% WBNP",

                                F10,

                                "WBNP

                             

                             

                            Why Subfields are also loading ? How to avoid them

                             

                            Thanks,

                            Siva

                          • Re: How to make Single QVD?
                            youssef belloum

                            You are talking about which fields ?