Load selected rows and transpose to columns

[qlikwiz123]

I have my data in a very inconvenient format in Excel.

I need to load only Row 18 and transpose the row values to column. This should exclude 'Avg' column in the end of the table.

Expected Output:

Month	Total
22-Aug	0.511
22-Sep	0.465
22-Oct	0.432
22-Nov	0.443
22-Dec	0.558
23-Jan	0.382
23-Feb	0.39
23-Mar	0.401
23-Apr	0.396
23-May	0.389
23-Jun	0.423
23-Jul	0.41

This has to be dymanic where if there are new months added in the Excel, the Qlik load should pick them up as well. I am unable to do this tricky load.

I have attached the sample data excel to this post.

[Vilo]

I believe you can accomplish what you are looking for with a 'CrossTable' load prefix. This is meant to transpose pivot table data. I was able to get the end result output with your sample data, just note that in the load script I had to divide the totals field by 2 due to the excel data already including the total on the bottom. If you leave the total row in place before sending to Qlik then this should work. Otherwise you can delete that row in your excel and Qlik will give you the totals on the front end for you (dont forget to remove the division if you do this).

Hopefully this is enough to get you going. Let me know if this is what you were looking for. I am attaching the .QVF file for your reference. 

Cross Table reference:

https://help.qlik.com/en-US/sense/August2023/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPrefix...

[qlikwiz123]

Hi @Vilo , thank you! This is only getting 2023 months and not before that. And while the data only has data until July 2023, this has until December 2023.

This isn't dynamic as you are explicitly mentioning months in the script you have data for. If the user adds more months in the future, this approach requires me editing the script again and again every time a new month is added.