I can not create QVD; How to resolve this error

[Anonymous]

I have the following error msg in the pic. I have 2 identical Fact tables. I am first storing them into QVDs. But its throwing an error. Any suggestions? I have attached the app here.

!

[#2]

Your tables are identical so Qlikview is Concatenating the tables for you automatically. I you don't want this to happen use the "Noconcatenate" keyword.  For example:

[Fact Table1]:
LOAD Date,
Total,
[Receipt #],
[Receipt Type],
[Qty Sold],
Payment,
Workstation,
Cashier,
[No. of Items],
Time,
Discount,
SubTotal,
Tax,
[Discount %],
[Last Name],
[First Name],
Status
// R
FROM
[..\Excel Files\Jan_sales_detail.xlsx]
(ooxml, embedded labels, table is [Sales Detail]);

store [Fact Table1] into Fact_table1.QVD;

NoConcatenate

Fact_table2:

LOAD Date,
Total,
[Receipt #],
[Receipt Type],
[Qty Sold],
Payment,
Workstation,
Cashier,
[No. of Items],
Time,
Discount,
SubTotal,
Tax,
[Discount %],
[Last Name],
[First Name],
Status
FROM
[..\Excel Files\Aug_sales_detail.xlsx]
(ooxml, embedded labels, table is [Sales Detail]);

store Fact_table2 into Fact_table2.QVD;

[#3]

Hi John,

Whenever you pull data from the same excel sheet just giving a different directory name (e.g. Fact_table2) won't help as it tries to concatenate the tables (since they have the same field names). Use the term "NoConcatenate" it will create two tables and join them using synthetic keys. Example given below:

StudentsDB1:

LOAD [Roll No.],

    Name,

    [Date of Birth],

    Gender

FROM

[Students Database.xls]

(biff, embedded labels, table is Sheet1$);

STORE StudentsDB1 into StudentsDB1.qvd(qvd);

NoConcatenate

StudentsDB2:

LOAD [Roll No.],

    Name,

    [Date of Birth],

    Gender

FROM

[Students Database.xls]

(biff, embedded labels, table is Sheet1$);

STORE StudentsDB2 into StudentsDB2.qvd(qvd);

Hope this helps.

Regards,

Janaki

[Anonymous]

try

[Fact Table1]:

LOAD Date,

     Total,

     [Receipt #],

     [Receipt Type],

     [Qty Sold],

     Payment,

     Workstation,

     Cashier,

     [No. of Items],

     Time,

     Discount,

     SubTotal,

     Tax,

     [Discount %],

     [Last Name],

     [First Name],

     Status

FROM

[..\Excel Files\Jan_sales_detail.xlsx]

(ooxml, embedded labels, table is [Sales Detail]);

store [Fact Table1] into Fact_table1.qvd(qvd);

DROP Table[Fact Table1];

[Fact Table2]:

LOAD Date,

     Total,

     [Receipt #],

     [Receipt Type],

     [Qty Sold],

     Payment,

     Workstation,

     Cashier,

     [No. of Items],

     Time,

     Discount,

     SubTotal,

     Tax,

     [Discount %],

     [Last Name],

     [First Name],

     Status

FROM

[..\Excel Files\Aug_sales_detail.xlsx]

(ooxml, embedded labels, table is [Sales Detail]);

store  [Fact Table2] into Fact_table2.qvd(qvd);

Drop Table [Fact Table2];

[maxgro]

your tables have the same number and names of fields

Qlik concatenates the second one to the first one and you only have one table in Qlik, [Fact Table1]

[Fact Table1]:

LOAD Date,  ......

FROM [..\Excel Files\Jan_sales_detail.xlsx] (ooxml, embedded labels, table is [Sales Detail]);

store [Fact Table1] into Fact_table1.QVD;

Fact_table2:

noconcatenate

LOAD Date,  .....

FROM .......;

store .....;

or if you don't need Fact Table 1, drop it after the store

[Fact Table1]:

LOAD Date,  ......

FROM [..\Excel Files\Jan_sales_detail.xlsx] (ooxml, embedded labels, table is [Sales Detail]);

store [Fact Table1] into Fact_table1.QVD;

drop table [Fact Table1];

Fact_table2:

LOAD Date,  .....

FROM .......;

store .....;

[Gabriel · Accepted solution]

Hi,

The reason for the error message is because the 2nd fact table has been concatenated to the 1st fact table, because of similar fields in both tables.

You have 2 options to resolve this:

1- Insert NoConcatenate after the 1st STORE , before the start of the 2nd table,

2- Option is to QUALIFY the 2nd table and STORE it. Not so good but it's possible

1st options:

[Fact Table1]:

...

...

..

FROM blah;

STORE INTO '\\blah';

NoConcatenate

Fact_table2:

...

...

..

FROM blah;

STORE INTO '\\blah';

2nd Options: This

[Fact Table1]:

...

...

..

FROM blah;

STORE INTO '\\blah';

QUALIFY *

Fact_table2:

...

...

..

FROM blah;

STORE INTO '\\blah';

But I prefer 1st option.

[Anonymous]

With drop table you assume what not will join  the tables, already what  only you will use to   create a qvd,

this way you  will can create as many  qvd's , even though repeat the same fields

[Fact Table1]:

LOAD Date,

   .....

FROM

[..\Excel Files\Jan_sales_detail.xlsx]

(ooxml, embedded labels, table is [Sales Detail]);

store [Fact Table1] into Fact_table1.qvd(qvd);

DROP Table[Fact Table1];

[Fact Table2]:

LOAD Date,

     ......

FROM

[..\Excel Files\Aug_sales_detail.xlsx]

(ooxml, embedded labels, table is [Sales Detail]);

store  [Fact Table2] into Fact_table2.qvd(qvd);

Drop Table [Fact Table2];

[Anonymous]

Thanks all for the help.