Compare the string and find the difference in Qliksense

[abhijith28]

HI,

There's a scenario where we have to compare the string and find the common string value grouped by ID.

Input 

Expected Output

Basically we have to create a column to find the common string when grouped by ID and the second column would be other part of the Name.

Tried previous, concat, subfield and peek functions but couldn't find the solution.

Exceptions: We should not consider delimiter  & subfield option to separate out the values

Thanks

[rwunderlich]

Can we assume the common string is always the beginning of the string? Or can the match be anywhere in the string?

-Rob

[abhijith28]

@rwunderlich 

That's Correct the common string will always starts from the beginning. 

[MarcoWedel · Accepted solution]

Maybe one solution might be:

table1:
LOAD * Inline [
Name,  ID
Abcd,  1
Abce,  1
Abcef, 1
Bcde,  2
c,     3
cd,    3
cde,   3
cdef,  3
defghi,4
defghj,4
defdhi,4
efgh,  5
fghi,  5
ghij,  5
];

table2:
LOAD *,
     Mid(Name,IterNo(),1)	as Char,
     IterNo()				as Pos
Resident table1
While IterNo()<=Len(Name);
Join
LOAD ID,
     Count(Name) as NameNum
Resident table1
Group By ID;

Join (table1)
LOAD ID,
     Index(Concat(CharEqual,'',Pos)&'0','0')-1 as CommonChars
Group By ID;
LOAD ID,
     Pos,
     fabs(Count(DISTINCT Char)=1 and Count(Char)=Only(NameNum)) as CharEqual
Resident table2
Group By ID, Pos;

DROP Table table2;

Join (table1)
LOAD ID,
     Name,
     Left(Name,CommonChars)		as Expected,
     Mid(Name,CommonChars+1)	as Expected1
Resident table1;

hope this helps

Marco

[abhijith28]

@MarcoWedel  Thanks for your time:)

Your solution is perfect.