wide format transfers into long format

[Coloful_Black]

I have a table like this 

Product name, Product code, Company, Month 1 unit, Month 1 dollar, Month 2 unit, Month 2 dollar .....Month 12 unit, Month 12 dollar.

I want to transform it into 

Product name, Product code, Company, Date , Dollar, Units

Date would be month 1 to month 12

So I tried to use cross table to make one for unit and one for dollar and then combine them together...

but due to unaligned key issue, do not get the right numbers...

wondering if we have a smarter way to achieve a format with long template.

thanks 

[rwunderlich]

Crosstable should work in this case. Assign a key to each row as you read it to ensure a unique key per row.

CrossTable (Month, Units, 4) Load RecNo() as RecId,
Product name, Product code, Company, Month 1 Unit, Month 2 Unit, etc...

CrossTable (Month, Units, 4) Load RecNo() as RecId,
Product name, Product code, Company, Month 1 dollar, Month 2 dollar, etc...

You can join the two tables using RecId. But do the crosstable first to a temp table and then join from the temp table. 

-Rob

[Coloful_Black]

that was my first try ...and I tried RecNo(), RowN0()...but all seems to have nonaligned key issue...and having recNo() into crosstable, it would generate non-unique id.....one row has multiple same RecId....

[Coloful_Black]

so I tried to create two additional temp file based on cross tab function to create rowID ( creating rowID when running corsstable generates multiple same rowID , which causes double counting when joins the tables)

however, the nonaligned rowID issue still retains. I tried recno() and rowno() , both do not give me any luck.

I create two tables one for units, one for dollar separately, their rowID do not a

wondering how the system generates the index.   would those dimension values 's difference change the sequence of system to load the data so that index would be changed as well? 

[rwunderlich]

Here's a working example:

DataTemp:
LOAD * Inline [
Product name, Product code, Company, Month 1 unit, Month 1 dollar, Month 2 unit, Month 2 dollar
Shoes, A, Comp1, 100, 400, 200, 500
Pants, B, Comp2, 50, 302, 60, 510
]
;

Units:
CrossTable (Date, Units, 4)
Load RecNo() as RecId, [Product name], [Product code], Company, [Month 1 unit] as 1, [Month 2 unit] as 2
Resident DataTemp;

Dollars:
CrossTable (Date, Dollars, 4)
Load RecNo() as RecId, [Product name], [Product code], Company, [Month 1 dollar] as 1, [Month 2 dollar] as 2
Resident DataTemp;

Join (Units) Load RecId, Date, Dollars
Resident Dollars;

Drop Table DataTemp, Dollars;

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[coloful_architect]

Hi Wunderlich

Thanks for your time looking into my question and appreciate your example code

I am attaching my QVF and the excel file for the raw data. It looks my code is pretty much like your example code...

but somehow I find a few discrepancies on the market share number. that brings me to think something wrong with the key to join two measurements.

For example, if you check RecID 865 ( PRD DEMO ID is DEMO003011), qlik shows market share % is 58%, but the raw data file is 36%

anything I miss? or it is due to the data per se? 

please advise.

thanks