topic Re: I can not create QVD; How to resolve this error in QlikView

I can not create QVD; How to resolve this error

Anonymous — Wed, 11 Mar 2015 21:07:59 GMT

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.

Re: I can not create QVD; How to resolve this error

Wed, 11 Mar 2015 21:26:12 GMT

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;

Re: I can not create QVD; How to resolve this error

Wed, 11 Mar 2015 21:30:52 GMT

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

Re: I can not create QVD; How to resolve this error

Anonymous — Wed, 11 Mar 2015 21:36:35 GMT

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];

Re: I can not create QVD; How to resolve this error

maxgro — Wed, 11 Mar 2015 22:34:15 GMT

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 .....;

Re: I can not create QVD; How to resolve this error

Gabriel — Wed, 11 Mar 2015 22:59:05 GMT

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.

Re: I can not create QVD; How to resolve this error

Anonymous — Wed, 11 Mar 2015 23:20:44 GMT

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];

Re: I can not create QVD; How to resolve this error

Anonymous — Thu, 12 Mar 2015 01:42:31 GMT

Thanks all for the help.