Crosstable or another way of tabletransforming

[beck_bakytbek]

Hi Folks,

i have a issue:

my table does look like:

following fields:

PROJNR, Empl1, Empl2, SalesEmpl1, SalesEmpl2

1415,  Jack,  Niko,  5, 15

my expected Output is:

PROJNR, Employee, Sales

1415, Jack, 5

1415, Niko, 15

i  tried to solve this issue by using of crosstable, but i didn't achieve any positive results.

Does anybody have any idea how to resolve this issue?

Any Feedbacks are appreciated.

Thanks a lot

beck

[Quy_Nguyen]

Hi,

Is the table format fixed as you describe? If so, I suggest a simple way like this:

Data:
Load 
   PROJNR,
   Empl1  as Employee,
   SalesEmpl1 as Sales
Resident [YourSource];
Load 
   PROJNR,
   Empl2  as Employee,
   SalesEmpl2 as Sales
Resident [YourSource];

[sunny_talwar · Accepted solution]

Can be automated if there are more Empl, SalesEmpl in your table

tmp1Table:
CrossTable (FieldName, Value)
LOAD * INLINE [
    PROJNR, Empl1, Empl2, SalesEmpl1, SalesEmpl2
    1415, Jack, Niko, 5, 15
];

tmp2Table:
LOAD PROJNR,
	 KeepChar(FieldName, '0123456789') as Num,
	 PurgeChar(FieldName, '0123456789') as FieldName,
	 Value
Resident tmp1Table;

DROP Table tmp1Table;

FinalTable:
LOAD DISTINCT PROJNR,
	 Num
Resident tmp2Table;

FOR i = 1 to FieldValueCount('FieldName')

	LET vFieldName = FieldValue('FieldName', $(i));
	
	Left Join (FinalTable)
	LOAD Distinct PROJNR,
	 	 Num,
	 	 Value as [$(vFieldName)]
	Resident tmp2Table
	Where FieldName = '$(vFieldName)';
	
NEXT

DROP Table tmp2Table;

[beck_bakytbek]

Sunny,

as always with only one swing of your QlikSense-sword  =), you have solved my issue, you are indeed the king of qlik sense

Thanks a lot for your help

[beck_bakytbek]

Nguyen

thanks a lot for your help