topic Re: Join and Syn key in QlikView

Join and Syn key

melissatw — Fri, 29 Dec 2017 11:08:11 GMT

Hello,

In my model I have 4 facts and for each of them one QVD per month. Here I would like to join every months for 2 facts in one Link table.

I tried many methods but in one case I have THE unique table with every fields but they are not linked (lines are duplicated) and in an other case I have 2 tables linked with a synthetic key (in this case my fields are well linked).

So now I don't know what to do to have only ONE table with every fields well linked.

This is my script code to have the first case:

T_LINK:
LOAD *
From $(vPathQVD)T_LINK_PREV_VENTES_0012017.QVD(qvd);

 FOR Each vFile in FileList('$(vPathQVD)T_LINK_PREV_VENTES*.QVD')
  T_LINK:
  Join LOAD *
  From $(vFile)(qvd);

 NEXT vFile

 FOR Each vFile in FileList('$(vPathQVD)T_LINK_SERVICE_CLIENT*.QVD')
  Join LOAD *
  From $(vFile)(qvd);

 NEXT vFile
EXIT Script;

This is my script code to have the second case:

T_LINK:
LOAD *
From $(vPathQVD)T_LINK_PREV_VENTES_0012017.QVD(qvd);

 FOR Each vFile in FileList('$(vPathQVD)T_LINK_PREV_VENTES*.QVD')
  T_LINK:
  Join LOAD *
  From $(vFile)(qvd);

 NEXT vFile


// Chargement des QVD T_LINK pour le Service Clients
 LOAD D*
 From $(vPathQVD)T_LINK_SERVICE_CLIENT_0012017.QVD(qvd);

 FOR Each vFile in FileList('$(vPathQVD)T_LINK_SERVICE_CLIENT*.QVD')
  Join LOAD *
  From $(vFile)(qvd);

 NEXT vFile
EXIT Script;

Sorry for my English, please do not hesitate to ask me more détails.

Thank you.

Re: Join and Syn key

sunny_talwar — Fri, 29 Dec 2017 12:46:24 GMT

May be try this

T_LINK:

LOAD *

From $(vPathQVD)T_LINK_PREV_VENTES_0012017.QVD(qvd);

FOR Each vFile in FileList('$(vPathQVD)T_LINK_PREV_VENTES*.QVD')

T_LINK_Temporary1:

LOAD *

From $(vFile)(qvd);

NEXT vFile

Join (T_LINK)

LOAD *

Resident T_LINK_Temporary1;

DROP Table T_LINK_Temporary1;

FOR Each vFile in FileList('$(vPathQVD)T_LINK_SERVICE_CLIENT*.QVD')

T_LINK_Temporary2:

LOAD *

From $(vFile)(qvd);

NEXT vFile

Join (T_LINK)

LOAD *

Resident T_LINK_Temporary2;

DROP Table T_LINK_Temporary2;

EXIT Script;

Re: Join and Syn key

melissatw — Fri, 29 Dec 2017 13:15:41 GMT

Thank you for your answer.

I tried your script but I have a syntax error on "join (T_LINK)" --> "table not found" while this one has been created just before

Re: Join and Syn key

sunny_talwar — Fri, 29 Dec 2017 13:21:39 GMT

Try this

T_LINK:

LOAD *,

0 as Dummy

From $(vPathQVD)T_LINK_PREV_VENTES_0012017.QVD(qvd);

FOR Each vFile in FileList('$(vPathQVD)T_LINK_PREV_VENTES*.QVD')

T_LINK_Temporary1:

LOAD *

From $(vFile)(qvd);

NEXT vFile

Join (T_LINK)

LOAD *

Resident T_LINK_Temporary1;

DROP Table T_LINK_Temporary1;

FOR Each vFile in FileList('$(vPathQVD)T_LINK_SERVICE_CLIENT*.QVD')

T_LINK_Temporary2:

LOAD *

From $(vFile)(qvd);

NEXT vFile

Join (T_LINK)

LOAD *

Resident T_LINK_Temporary2;

DROP Table T_LINK_Temporary2;

DROP Field Dummy;

EXIT Script;

Re: Join and Syn key

melissatw — Fri, 29 Dec 2017 14:07:48 GMT

Really thank you, it works!!

Can you explain to me the need for the field Dummy please?

Re: Join and Syn key

sunny_talwar — Fri, 29 Dec 2017 14:10:59 GMT

Because we were doing * load from files which have the same field names, they get autoconcatenated. In order to stop that from happening, I simply added a dummy field to the first load.

Re: Join and Syn key

melissatw — Fri, 29 Dec 2017 14:19:24 GMT

ok thanks for the explanation.

Have a good day