topic Re: Data Transformation in script in QlikView

Data Transformation in script

chhavi376 — Tue, 02 Oct 2018 03:47:14 GMT

Hi All,

I have data in this format from the excel:

I want to transform it like this:

Can anyone help?

Thanks,

Chhavi

Re: Data Transformation in script

Anonymous — Tue, 02 Oct 2018 06:43:22 GMT

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,

Re: Data Transformation in script

tomasz_tru — Tue, 02 Oct 2018 07:28:23 GMT

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

Re: Data Transformation in script

Peter_Cammaert — Tue, 02 Oct 2018 07:54:26 GMT

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...

Re: Data Transformation in script

jubarrosor — Tue, 02 Oct 2018 08:00:14 GMT

Hi:

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

Best Regards,

Juan P. Barroso