Data Transformation in script

[chhavi376]

Hi All,

I have data in this format from the excel:

I want to transform it like this:

Can anyone help?

Thanks,

Chhavi

[Anonymous]

Good day

This can be accomplished, but it will take some playing around with the data.

When importing your file using the Table Files option in your script viewer - once the file has been selected, select the "Enable transformation step" .  You will need to get the data to a point where the Product1 field and the Date columns are all aligned (This means that you need to remove the blank rows and the Quarter sections).  Once this has been done use the Rotate function that is also in the Transformation steps to pivot the table to get the desired output.

Regards,

[tomasz_tru]

You can play with Crosstable function (https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/ScriptPrefixes...). But using such a file will make your application more prone for human error...

[Peter_Cammaert]

You have no means of accessing the data source from which Excel assembles this overview?

That would make ETL a lot simpler. Now you are facing the pretty complex task of tearing your Excel to pieces and trying to make sense of some of them with number formatting that varies depending on the row header...

[jubarrosor]

Hi:

You must use the crosstable function and remove the lines that do not contain information using a where.

Best Regards,

Juan P. Barroso