Data Transpose

[qv11user]

Hi Team,

Hope you are doing well!

I have data like below

Business		HI:AA	HI:AB	HI:AC
EIM-01-AA	No of Records	1390	1500	1700
Gross value	No of Defects	19	22	30
EIM-02-AA	No of Records	1490	1550	1800
Net Value	No of Defects	29	31	40
Aap Sector	No of Records	1309	1700	1800
	No of Defects	101	130	140
EIM-31-BC	No of Records	1490	1340	1900
Coo_Existence	No of Defects	12	40	90
EIM-43-CD	No of Records	1200	1300	1400
ER Val	No of Defects	80	19	29
Export Controls	No of Records	1290	1430	1300
	No of Defects	100	290	190

I want to pick the following values from Business column:

• Values which starts with EIM and the very next value

For example: below we have EIM-02-AA, Net Value and Aap Sector value but we need to pick only EIM-02-AA, Net Value values

Values as per data	Required values
EIM-02-AA	EIM-02-AA
Net Value	Net Value
Aap Sector

After that I want to transform data like below

Business	ID	No of Records	No of Defects
EIM-01-AA	HI:AA	1390	19
EIM-01-AA	HI:AB	1500	22
EIM-01-AA	HI:AC	1700	30
EIM-02-AA	HI:AA	1490	29
EIM-02-AA	HI:AB	1550	31
EIM-02-AA	HI:AC	1800	40
EIM-31-BC	HI:AA	1490	12
EIM-31-BC	HI:AB	1340	40
EIM-31-BC	HI:AC	1900	90
EIM-43-CD	HI:AA	1200	80
EIM-43-CD	HI:AB	1300	19
EIM-43-CD	HI:AC	1400	29

Note: EIM-01-AA, Gross Value should merge into one business (EIM-01-AA in above table) and EIM-02-AA, Net Value should merge into one business (EIM-02-AA in above table).

Please help me here.

[Brett_Bleess]

See the following Design Blog post, it should help you with your use case:

https://community.qlik.com/t5/Qlik-Design-Blog/The-Crosstable-Load/ba-p/1468083

Regards,
Brett

[Saravanan_Desingh]

Please check this solution