Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

siva_boggarapu
Contributor II

How to make Single QVD?

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

10 Replies
niclaz79
Contributor III

Re: How to make Single QVD?

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
Esteemed Contributor

Re: How to make Single QVD?

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
Esteemed Contributor

Re: How to make Single QVD?

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
Contributor II

Re: How to make Single QVD?

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

b_knuever
Contributor III

Re: How to make Single QVD?

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
Contributor II

Re: How to make Single QVD?

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

b_knuever
Contributor III

Re: How to make Single QVD?

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
Esteemed Contributor

Re: How to make Single QVD?

You are talking about which fields ?

siva_boggarapu
Contributor II

Re: How to make Single QVD?

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

Community Browser