Combine multiple dimensions into one.

[aolaleye]

Hello Qlik folks:

I am building a report with  2 dimensions  OLDSTUDENTS and NEWSTUDENTS with some values that are common to both. I need to create a single dimension that displays a distinct list from both dimensions. Sort of like a UNION in a sql query.

For example:

NEWSTUDENTS

JOE Sam

Bob Deer

George Paul

OLDSTUDENTS

JOE Sam

Frank Stein

Tony Tiger

OLDNEWSTUDENTS

JOE Sam

Bob Deer

George Paul

Frank Stein

Tony Tiger

Can anyone suggest a way of doing this? Preferably in the Expression editor using a function?

[Vegar]

It they are in the same table, but only one of the two got a value per row then you could try to create a calcukated dimension named OLDNEWSTUDENTS like this.

=OLDSTUDENTS & NEWSTUDENTS

If this approach doesn't work you will probably need to make adjustments to your script. I

[aolaleye]

Hello @Vegar  Thanks for your response. I tried the concatenation option you suggested and it worked.

[aolaleye]

I am trying to get all the unique values both fields to appear in a single list. The concatenation option gives  the combination of both fields which is different. Any thoughts?

[Vegar]

You will most likely need to alter your script/data model.

If the fields found in the same table in your data model then you can concat the field values into one field and then create a link table that contains the field value.

set NullInterpret ='';
LOAD 
	*, 
	NEWSTUDENTS &'|'& OLDSTUDENTS as %students 
inline [
NEWSTUDENTS,	OLDSTUDENTS,
JOE Sam, 
Bob Deer,
George Paul,
,	JOE Sam
, 	Frank Stein
,	Tony Tiger
];

For each _student in FieldValueList('%students')

Dim_Student:
LOAD *
WHERE LEN(OLDNEWSTUDENTS) >0;
LOAD 
	'$(_student)' as %students,
SubField('$(_student)', '|') as OLDNEWSTUDENTS
AutoGenerate 1

;

next 

[mahaveerbiraj]

HI Aolaleye,

I hope we can solve your problem using exist() function in qliksense 

try like below,

Student_Table:

load *

inline

[NEWSTUDENTS, OLDSTUDENTS,
JOE Sam,
Bob Deer,
George Paul,
, JOE Sam
, Frank Stein
, Tony Tiger

];

NoConcat  

Final_Student_Table:

Load

NEWSTUDENTS as NewOldStudent

resident  Student_Table where  len(NEWSTUDENTS) >0;

Concat 

load  

OLDSTUDENTS as NewOldStudent

resident  Student_Table where len(OLDSTUDENTS )>0 and  not exist(OLDSTUDENTS ,NewOldStudent);