Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
aolaleye
New Contributor II

Combine multiple dimensions into one.

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?

5 Replies
Partner
Partner

Re: Combine multiple dimensions into one.

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

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
aolaleye
New Contributor II

Re: Combine multiple dimensions into one.

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

aolaleye
New Contributor II

Re: Combine multiple dimensions into one.

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?

Partner
Partner

Re: Combine multiple dimensions into one.

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 

 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
mahaveerbiraj
Contributor

Re: Combine multiple dimensions into one.

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