Join and Syn key

[melissatw]

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.

[sunny_talwar]

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]

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

[sunny_talwar · Accepted solution]

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]

Really thank you, it works!!

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

[sunny_talwar]

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]

ok thanks for the explanation.

Have a good day