Load xls files loop with Left Join

[Anonymous]

Hi everybody, 

I'm loading multiple files that I concatenate with the following script :

For Each nomfic in 'Fica', 'Ficb', , 'Ficc'

result:

Load A,B, C, D from $(nomfic);

LEFT JOIN (result)

LOAD A,E from $(nomfic);

next

It works perfectly without the LEFT JOIN.

I don't know how to make it work with LEFT JOIN so that the files concatenate ?

Each file has to be left joined and then concatenate.

[kaushiknsolanki · Accepted solution]

Try this.

For Each nomfic in 'Fica', 'Ficb', , 'Ficc'

Temp:

Load A,B, C, D from $(nomfic);

LEFT JOIN (Temp)

LOAD A,E from $(nomfic);

Result:

Load *,'$(nomfic)' as Source

Resident Temp;

Drop table Temp;

next

Regards,

Kaushik Solanki

[marcus_sommer]

You need in each case an additional step maybe the following:

For Each nomfic in 'Fica', 'Ficb', , 'Ficc'

     result:

     Load A,B, C, D from $(nomfic);

          LEFT JOIN (result)

     LOAD A,E from $(nomfic);

     store result into result_$(nomfic).qvd (qvd);

     drop tables result;

next

result: load * from result_*.qvd (qvd);

- Marcus

[Anonymous]

Thanks Marcus,

The Kaushik solution seems perfect. I prefer to avoid generating intermediate qvds.

[Anonymous]

Thanks Kaushik for this nice solution.