Qlik script - concatenated excel

[markobanjanin]

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. 

[Channa]

try with CrossTable

[markobanjanin]

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

[rogerpegler]

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]

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.