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

[niclaz79]

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.

[YoussefBelloum]

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)

[YoussefBelloum]

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

[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

[bramkn]

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.

[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

[bramkn]

I don't know what you mean by subfields. But if you are talking about the fields that should only be in one of the tables. You should start by adding a where condition using the flag and then you will have to delete fields that have only null values.

[YoussefBelloum]

You are talking about which fields ?

[siva_boggarapu]

Hi,

Please look at the excel sheet. There are 3months and 12 months data is there. I want make them into one SIngle QVD.

Can you help how to do that with qvf file?

Thanks,

Siva