topic Compare the string and find the difference in Qliksense in App Development

Compare the string and find the difference in Qliksense

abhijith28 — Fri, 24 Dec 2021 12:42:16 GMT

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

Re: Compare the string and find the difference in Qliksense

rwunderlich — Fri, 24 Dec 2021 15:56:10 GMT

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

-Rob

Re: Compare the string and find the difference in Qliksense

abhijith28 — Mon, 27 Dec 2021 06:32:55 GMT

@rwunderlich

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

Re: Compare the string and find the difference in Qliksense

MarcoWedel — Mon, 27 Dec 2021 22:58:17 GMT

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

Re: Compare the string and find the difference in Qliksense

abhijith28 — Wed, 29 Dec 2021 09:08:45 GMT

@MarcoWedel Thanks for your time:)

Your solution is perfect.