topic How to transform table in App Development

How to transform table

Kaushik2020 — Wed, 19 Apr 2023 10:15:24 GMT

Hi All, I have a table where Price type is mentioned in rows. I want to see the Price type is different columns. Please see below example data.

//Current view of data:

Product Name	Product Type	Price Type	Value
CISCO 1941/K9 ROUTER	Router	one time	100.25
CISCO 1941/K9 ROUTER	Router	recurring	95.55
CISCO 4331/K9 Router	Router	one time	100.25
CISCO 4331/K9 Router	Router	recurring	95.55
CISCO ISR 1001 Router	Router	one time	100.25
CISCO ISR 1001 Router	Router	recurring	95.55

//Expected view:

Product Name	Product Type	one time	recurring
CISCO 1941/K9 ROUTER	Router	100.25	95.55
CISCO 4331/K9 Router	Router	100.25	95.55
CISCO ISR 1001 Router	Router	100.25	95.55

thanks in advance.

Re: How to transform table

MayilVahanan — Wed, 19 Apr 2023 10:21:03 GMT

Try with Generic load, hope it helps

Generic
LOAD * INLINE [
Product Name, Product Type, Price Type, Value
CISCO 1941/K9 ROUTER, Router, one time, 100.25
CISCO 1941/K9 ROUTER, Router, recurring, 95.55
CISCO 4331/K9 Router, Router, one time, 100.25
CISCO 4331/K9 Router, Router, recurring, 95.55
CISCO ISR 1001 Router, Router, one time, 100.25
CISCO ISR 1001 Router, Router, recurring, 95.55
];

The Generic Load - Qlik Community - 1473470

Re: How to transform table

Gabbar — Wed, 19 Apr 2023 12:24:26 GMT

Try this:
SourceTable:
Load * from Source;

A:
Load ProductName,ProductType,Value as OneTime
resident SourceTable where PriceType = 'OneTime';
Left join
B:
Load ProductName,ProductType,Value as recurring
resident SourceTable where PriceType = 'recurring';

Drop Table SourceTable;

(In your Data You have Mentioned Only two product Types, If there are many different Product types you can use variable and loop to do it)