Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
If you are a Qlik Insight Bot user, join this collaborative group: JOIN GROUP
Highlighted
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
Highlighted

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;

View solution in original post

6 Replies
Highlighted

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;

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

Highlighted

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;

View solution in original post

Highlighted
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?

Highlighted

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.

Highlighted
New Contributor II

Re: Join and Syn key

ok thanks for the explanation.

Have a good day