Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
markobanjanin
New Contributor III

Qlik script - concatenated excel

Hi,

i have an excel file that I need to parse and load in a qvd. The issue with the excel file is it’s structure. 

First row contains user ids and pivoted days on a month (1 - 31). These are table headers  

say I have 10 users.

2nd row would display month name and then from the 3rd to 12th row I’d get the list of users and their daily sales. 13th row would display next months name and 14-23rd the list of users with daily sales. And so forth. 

How can I parse this file to bring it to tabluar structure?

thank you. 

Labels (2)
4 Replies
Channa
Contributor III

Re: Qlik script - concatenated excel

try with CrossTable

Channa
markobanjanin
New Contributor III

Re: Qlik script - concatenated excel

I’m afraid it won’t work since my id column carries user ids and month names

rogerpegler
Contributor

Re: Qlik script - concatenated excel

I think you need to process it in the load script in chunks.  

Load it all into a staging table. Then load just the January ID rows ie 3 to 12 into it's own table which can then be processed via the cross table function. The repeats for Feb etc.

How you identify the subsets of rows and loop through the data will depend on the Excel structure. If it's a fixed number of rows, you might be able to effectively hard code the rows. If you need flexibility, perhaps using inter-record functions such as previous can be used.

markobanjanin
New Contributor III

Re: Qlik script - concatenated excel

I like that approach. Would you further elaborating on the previous function? Attached is an example - rows can grow from one month to the other.