Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
melissatw
New Contributor II

Join and Syn key

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.

1 Solution

Accepted Solutions

Re: Join and Syn key

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;

6 Replies

Re: Join and Syn key

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;

melissatw
New Contributor II

Re: Join and Syn key

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

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;

melissatw
New Contributor II

Re: Join and Syn key

Really thank you, it works!!

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

Re: Join and Syn key

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.

melissatw
New Contributor II

Re: Join and Syn key

ok thanks for the explanation.

Have a good day

Community Browser