topic Re: Date Synthetic Key File Name Date# in QlikView

Date Synthetic Key File Name Date#

nicolearayav — Sat, 16 Nov 2024 18:03:26 GMT

Hello Everybody,

Please your help with the following:

I have 3 tables that im taking the date from the File Name:

Table1:
LOAD
FileName() as FileName,
Date(Date#((Left(Right(FileName(),12),7)),'MM.YYYY'),'MM.YYYY') as FileDate,

Table2:
LOAD
FileName() as FileName,
Date(Date#((Left(Right(FileName(),12),7)),'MM.YYYY'),'MM.YYYY') as FileDate,

Table3:
LOAD
FileName() as FileName,
Date(Date#((Left(Right(FileName(),12),7)),'MM.YYYY'),'MM.YYYY') as FileDate,

And this create a Synthetic Key, how can I get rid of the key?
I tried to put other names to FileName as FileName1 but I had to create different filters, I need to have one filter for date when I select a month, all my graphics take that month.

Thanks!!

Re: Date Synthetic Key File Name Date#

Kushal_Chawda — Fri, 25 Sep 2020 20:21:25 GMT

@nicolearayav try to concatenate tables

Table1:
LOAD
FileName() as FileName,
Date(Date#((Left(Right(FileName(),12),7)),'MM.YYYY'),'MM.YYYY') as FileDate,

concatenate

Table2:
LOAD
FileName() as FileName,
Date(Date#((Left(Right(FileName(),12),7)),'MM.YYYY'),'MM.YYYY') as FileDate,

concatenate

Table3:
LOAD
FileName() as FileName,
Date(Date#((Left(Right(FileName(),12),7)),'MM.YYYY'),'MM.YYYY') as FileDate,

Re: Date Synthetic Key File Name Date#

nicolearayav — Fri, 25 Sep 2020 20:40:22 GMT

Hi,

It doesnt work because I have

Table1:
LOAD
FileName() as FileName,
Date(Date#((Left(Right(FileName(),12),7)),'MM.YYYY'),'MM.YYYY') as FileDate,

....

Join

TableA:

load.....

Table2:
LOAD
FileName() as FileName,
Date(Date#((Left(Right(FileName(),12),7)),'MM.YYYY'),'MM.YYYY') as FileDate,

....

Join

TableB:

load.....

Table3:
LOAD
FileName() as FileName,
Date(Date#((Left(Right(FileName(),12),7)),'MM.YYYY'),'MM.YYYY') as FileDate,

....

Join

TableC:

load.....

I tried to put concatenate between after the table with the Join:
Example:

Table1:
LOAD
FileName() as FileName,
Date(Date#((Left(Right(FileName(),12),7)),'MM.YYYY'),'MM.YYYY') as FileDate,

....

Join

TableA:

load.....

Concatenate

Table2:
LOAD
FileName() as FileName,
Date(Date#((Left(Right(FileName(),12),7)),'MM.YYYY'),'MM.YYYY') as FileDate,

....

Join

TableB:

load.....

Concatenate

Table3:
LOAD
FileName() as FileName,
Date(Date#((Left(Right(FileName(),12),7)),'MM.YYYY'),'MM.YYYY') as FileDate,

....

Join

TableC:

load.....

But it doenst work 😞

Re: Date Synthetic Key File Name Date#

Brett_Bleess — Tue, 20 Oct 2020 16:03:32 GMT

Here are some Design Blog posts that may be of some help:

https://community.qlik.com/t5/Qlik-Design-Blog/Synthetic-Keys/ba-p/1472634

https://community.qlik.com/t5/Qlik-Design-Blog/To-Join-or-not-to-Join/ba-p/1463102

Here is the base URL for that area in case you want to search further on your own for other posts:

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett

Re: Date Synthetic Key File Name Date#

Kushal_Chawda — Tue, 20 Oct 2020 16:30:29 GMT

@nicolearayav try below then

Table1: LOAD FileName() as FileName, Date(Date#((Left(Right(FileName(),12),7)),'MM.YYYY'),'MM.YYYY') as FileDate, ....; Join TableA: load.....; Table2: noconcatenate LOAD FileName() as FileName, Date(Date#((Left(Right(FileName(),12),7)),'MM.YYYY'),'MM.YYYY') as FileDate, ....; Join TableB: load.....; Table3: noconcatenate LOAD FileName() as FileName, Date(Date#((Left(Right(FileName(),12),7)),'MM.YYYY'),'MM.YYYY') as FileDate, ....; Join TableC: load.....; Final: LOAD * resident Table1; concatenate LOAD * resident Table2; concatenate LOAD * resident Table3; Drop tables Table1,Table2,Table3;