topic Re: wide format transfers into long format in App Development

wide format transfers into long format

Coloful_Black — Thu, 02 Nov 2023 21:47:14 GMT

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

Re: wide format transfers into long format

rwunderlich — Thu, 02 Nov 2023 23:24:28 GMT

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

Re: wide format transfers into long format

Coloful_Black — Fri, 03 Nov 2023 00:43:12 GMT

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

Re: wide format transfers into long format

Coloful_Black — Fri, 03 Nov 2023 01:58:24 GMT

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?

Re: wide format transfers into long format

rwunderlich — Fri, 03 Nov 2023 16:49:40 GMT

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

Re: wide format transfers into long format

coloful_architect — Tue, 07 Nov 2023 01:27:14 GMT

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