Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
Hello @Vegar Thanks for your response. I tried the concatenation option you suggested and it worked.
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?
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
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);