Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Loading data with Changing columns in Script

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 

MatchTradeID PartyCPTypeTrade IDProduct ClassP/RNotionalCur

 

on 21/01/2010

MatchTradeIDTypeTrade IDProduct ClassP/RNotionalCurNotional2Cu2Trade CommentsBreak 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

Labels (3)
4 Replies
Highlighted
Partner
Partner

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. 

Highlighted
Contributor III
Contributor III

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

Highlighted

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Highlighted
Contributor III
Contributor III

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