Loading data with Changing columns in Script

[hitha1512]

Hi Team,

I have a scenario where i receive files on daily basis with changing columns based on which i have to develop a report. 

The columns that have to go into the report is defined. 

Eg : On 19/01/2020 i receive file with columns 

Match

TradeID

Party

CP

Type

Trade ID

Product Class

P/R

Notional

Cur

on 21/01/2010

Match

TradeID

Type

Trade ID

Product Class

P/R

Notional

Cur

Notional2

Cu2

Trade Comments

Break Categories

This Change in column/deletion of column happens at the source when a particular column does not have any data.

i have tried below approach -

 > load *, but due to multiple files its creating more than 20 synthetic keys within script 

 > Created a dummy table and concatenated it with required table. it took ages to load. (file size is max 40 KB each, as of now i have 13 files)

is there any other way to load this kind of file ????

Thank you,

Hitha Dhani

[Stoyan_Terziev]

Hi Hitha,

I think your second approach is quite close to what you want to achieve. The slowness makes me think there is room for improvement, so can you try doing it like this:

FactData:
LOAD
	'' as EmptyField
AutoGenerate 0
;


Concatenate (FactData)
LOAD
	*
FROM Files*.qvd (qvd)
;

I hope that helps!

Kind regards,

S.T. 

[hitha1512]

Hi Stoyan,

Thank you for your response. Please find the code i used below.... i used the .xls files directly and you have used qvd files. Should i store every file into .qvd and then load ? will that help ??

Main_Temp:

Load 0 as Dummy
AutoGenerate 0;

Concatenate(Main_Temp)

LOAD *
FROM
MTM*.xlsx; 

Drop Field Dummy;  

Regards,

Hitha Dhani

[jonathandienst]

2 Points:

1) If you use a field that will be in the final table in your AutoGenerate 0, then you will not need a drop field statement. Make sure you use '' for text and 0 for numerics as Qlik uses the first time it sees the field to infer the data type.

2) You can stage the xlsx files as  qvds in a 2 tier ETL if you like, and this will improve performance if the staging load only happens once per day and the main document reloads may happen several times a day, or you are loading a bunch of invariant history files on every load.

[hitha1512]

Hi Jonty,

Observed that the high load time was due to the Load * from second part of the excel. Due to the bad file format, instead of 20-25 columns , 16,000+ columns are getting loaded from each file. which means 16000 * no. of files would get loaded each time.

i tried to get only required columns loaded, did not work. Could you please let me know if there is a way to restrict the number of columns to be loaded while doing a load*. 

Regards,

Hitha Dhani