Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Master
Master

Crosstable or another way of tabletransforming

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

 

1 Solution

Accepted Solutions
Highlighted

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;

View solution in original post

4 Replies
Highlighted
Creator III
Creator III

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];

 

Highlighted

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;

View solution in original post

Highlighted
Master
Master

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

Highlighted
Master
Master

Nguyen

thanks a lot for your help