Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

chhavi376
Contributor II

Data Transformation in script

Hi All,

I have data in this format from the excel:

q.PNG

I want to transform it like this:

q2.PNG

Can anyone help?

Thanks,

Chhavi

4 Replies
Highlighted
qlikmeplease
Contributor III

Re: Data Transformation in script

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
Valued Contributor

Re: Data Transformation in script

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

Re: Data Transformation in script

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

Luminary
Luminary

Re: Data Transformation in script

Hi:

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

Best Regards,

Juan P. Barroso