Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi ,How to left join 2 tables in the below scenario:
Table A
Field Present-Node
Table B
Field Present-Brief Description
I want to have all rows of Table A and Common rows from Table B(Left Join).
Now,How can the join be made,if Node field values are present as a sub string in Brief Description Column.
Ex:
Table A-
Node
abc
def
Table B-
Brief Description Time
123abc11231 10 Nov
31231231def412123 11 Nov
Output:
Table A-
Node Brief Description Time
abc 123abc11231 10 Nov
def 31231231def412123 11 Nov
The below Works
TableA:
LOad * inline [
Node
def
abc
];
mapA:
Mapping
LOAD *,
'@-'&Node&'-@'
Resident TableA;
Drop table TableA;
OUTPUT:
LOAD *, TextBetween(MapSubString('mapA',BriefDescription),'@-','-@') as Node
inline [
BriefDescription,Time
123abc11231,10 Nov
31231231def412123,11 Nov
];
Match on text in Brief Description? Meaning everything else will be numbers of could it be like this
123abc11231xyz? and match needs to be on abc only?
Have you tried figuring out what are the prefix and suffixes?
No,not necessarily numbers. Could be anything. just the exact string from Node field has to be present in Brief desc field.
The below Works
TableA:
LOad * inline [
Node
def
abc
];
mapA:
Mapping
LOAD *,
'@-'&Node&'-@'
Resident TableA;
Drop table TableA;
OUTPUT:
LOAD *, TextBetween(MapSubString('mapA',BriefDescription),'@-','-@') as Node
inline [
BriefDescription,Time
123abc11231,10 Nov
31231231def412123,11 Nov
];
if your Query is resolved please close the thread
Qlik Community Tip: Marking Replies as Correct or Helpful
MapSubString is something new for me.
Thank you.
///Can USe PurgeChar:
/// purgeChar([Brief Description],'1,2,3,4,5,6,7,8,9,0') as Node,
[Table1]:
load * Inline
[ Node
abc
def
];
[Table B]:
load
purgeChar([Brief Description],'1,2,3,4,5,6,7,8,9,0') as Node,
[Brief Description],
Time;
LOAD * Inline
[
Brief Description , Time
123abc11231 , 10 Nov
31231231def412123 ,11 Nov
];
drop Table [Table1];
Hi Apoorva,
We cannot use purgeChar because as mentioned, the brief description can be combination of numbers and characters + Node.
Else this would have been the best option.