topic Re: Load xls files loop with Left Join in QlikView

Load xls files loop with Left Join

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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 — Wed, 21 Feb 2018 15:59:53 GMT

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;

Regards,

Kaushik Solanki

marcus_sommer — Wed, 21 Feb 2018 16:01:18 GMT

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 — Wed, 21 Feb 2018 16:35:10 GMT

Thanks Marcus,

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

Anonymous — Wed, 21 Feb 2018 16:35:41 GMT

Thanks Kaushik for this nice solution.