Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
chhavi376
Creator II
Creator 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
qlikmeplease
Creator III
Creator III

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

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

jubarros
Luminary Alumni
Luminary Alumni

Hi:

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

Best Regards,

Juan P. Barroso