topic Re: Date & Synthetics Key in QlikView

Date & Synthetics Key

tracycrown — Sat, 25 Jan 2014 07:40:16 GMT

Hi all

Can someone help to solve my problem on date (Jan-13) and synthetics keys (Outlet, SKU, Date ...), please refer to attached file for details ?

Thanks Tracy

Re: Date & Synthetics Key

phaneendra_kunc — Sat, 25 Jan 2014 08:05:42 GMT

see if attached helps...

Re: Date & Synthetics Key

tracycrown — Sat, 25 Jan 2014 09:04:45 GMT

Dear Phaneendra

Thank you so much.

Although synthetic key has been solved, no data shown in Vendor table and the date field (41306) is different from Excel (Jan-13). Please help.

Thank you, Tracy

Re: Re: Date & Synthetics Key

phaneendra_kunc — Sat, 25 Jan 2014 09:43:04 GMT

see attached...

Re: Re: Date & Synthetics Key

tracycrown — Sat, 25 Jan 2014 10:44:41 GMT

Dear Phaneendra

Thank for your help.

When I first open your file, I saw everything on the screen and date problem had been solved.

However, when I RELOAD the file, no data appears on screen, please advise.

Thank you, Tracy

Re: Date & Synthetics Key

phaneendra_kunc — Sat, 25 Jan 2014 23:58:15 GMT

Are you using the same Test.Xls file that you have attached?

save your excel and my QVW in same location and perform reload.

Re: Date & Synthetics Key

tracycrown — Sun, 26 Jan 2014 05:00:40 GMT

Dear Phaneendra

Yes, I had done that but I also modified the followings due to my errors in original script and table :

1. Changed the script error in Customer from 5 to 4, see below:

CrossTable(Date, Cust_Data,4)

Load *.FROM

Test.xls

2. Changed Customer to Vendor

Before : KPI, Customer, Outlet, SKU.......

After : KPI, Vendor, Outlet, SKU .......

Nothing shown on the screen after making above changes, please advise why this happens ?.

Thank you very much , Tracy

Re: Date & Synthetics Key

brindlogcool — Sun, 26 Jan 2014 05:17:11 GMT

Can you attach the modified files?

Re: Re: Date & Synthetics Key

tracycrown — Sun, 26 Jan 2014 09:52:40 GMT

Dear Brindlogcool

Please refer to the modified files.

Thanks, Tracy

Re: Re: Re: Date & Synthetics Key

brindlogcool — Sun, 26 Jan 2014 13:10:01 GMT

Hi Tracy,

What Phaneendra given is correct.

The Fact table is getting dropped hence you are not able to see the data. I have attached the modified file for your reference.

When the tables have same column names it will automatically get concatenated in QlikView. So in your script the

1.Temp_Fact and Fact table become one table as Temp_Fact through auto concatenation(Both tables had same column name).

2. When you drop the Temp_Fact the Fact table is dropped and hence no data to display.

I just added the NoConcatenate in the script for the Fact table

Fact:

NoConcatenate

Load Date(replace(Date,'/','-'),'DD-MM-YYYY') as Date,

Vend_Data,

SKU,

Outlet,

CKPI,

Cust_Data,

Customer,

Vendor,

VKPI

Resident Temp_Fact;

Re: Re: Re: Date & Synthetics Key

tracycrown — Mon, 27 Jan 2014 03:20:28 GMT

Dear Phaneendra and Brindlogcool

I really appreciate your kind effort in resolving my problem, you are all very helpful and knowledgeable.

Other:

Please enlighten me on the purpose/function of NoCancatenate ?.

Thank you so much, Tracy

Re: Date & Synthetics Key

Anonymous — Mon, 27 Jan 2014 03:25:34 GMT

When QlikView sees two tables with the exact same field names it will Automatically concatenate the tables for you. This is to avoid synthetic keys. You can tell QlikView to stop this behavior by putting the NoConcatenate function before the second table to keep it from happening. So that, in your case, you may drop the second table in a later part of the script and not drop your entire fact table.

Re: Re: Re: Date & Synthetics Key

brindlogcool — Mon, 27 Jan 2014 03:25:55 GMT

In QlikView, If all the columns are identical in one or more tables then it will get appended and become one single table. To avoid it we use the NoConcatenate.

The NoConcatenate prefix forces two loaded tables with identical field sets to be treated as two separate internal tables, when they otherwise would be automatically be concatenated and become one table.

It was happened the Temp_Fact and Fact become one table without the NoConcatenate.

Re: Re: Re: Date & Synthetics Key

tracycrown — Mon, 27 Jan 2014 03:34:49 GMT

Dear Phaneendra and Brindlogcool

I just found out that the data in all months are incorrect (ie. different from Test.xls), please advise.

Thank you, Tracy

Re: Re: Re: Re: Date & Synthetics Key

brindlogcool — Mon, 27 Jan 2014 04:05:36 GMT

Hi Tracy,

Check this file... Can you please provide more details on the issue?

Re: Re: Re: Re: Re: Date & Synthetics Key

tracycrown — Mon, 27 Jan 2014 05:58:40 GMT

Dear Brindlogcool

Please just look at first record of Customer file :

Excel :

CKPI, Customer, Outlet, SKU, Amount

No. of Customer, 1901, OU1, SKU1, 300.00

Qlikview :

CKPI, Customer, Outlet, SKU, Amount

No. of Customer, 1901, OU1, SKU1, 600.00

Hope you can see the difference and similar problem in Vendor file.

Thank you for your kind attention, Tracy

Re: Re: Re: Re: Date & Synthetics Key

tracycrown — Sun, 02 Feb 2014 02:50:16 GMT

Dear all

Can someone advise why the data are duplicated (eg 300 x 2) for all records after Concatenate and NoConcatenate ?

Thank you, Tracy