Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Qlik Community
- :
- Forums
- :
- Analytics
- :
- New to Qlik Analytics
- :
- Whilst is equal…

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

HonestToad

Partner - Contributor II

2022-03-11
12:00 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Whilst is equal…

This is an example in MatLab which I am trying to replicate in Qlik. I have lots of random length, random sub-fielded strings that I would like to remove the preceding text that is common to all

% The data

DriftEL = 'ta_tsc_tsc_mcs_hk_atc_gyro_drift_el';

DriftXEL = 'ta_tsc_tsc_mcs_hk_atc_gyro_drift_xel';

DriftLOS = 'ta_tsc_tsc_mcs_hk_atc_gyro_drift_los';

% The algorithm [Strip off first character if shared by all others.]

while isequal(DriftEL(1),DriftXEL(1),DriftLOS(1))

DriftEL(1) = [];

DriftXEL(1) = [];

DriftLOS(1) = [];

end

% Result

DriftEL(1) = el;

DriftXEL(1) = xel;

DriftLOS(1) = los;

I am sure there is more than one methodology to do this in Qlik, I would welcome and pointers

341 Views

1 Solution

Accepted Solutions

HonestToad

Partner - Contributor II

2022-03-17
09:24 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Having thought about it all weekend and bouncing ideas off a colleague, I have come up with a solution that works. It may not be the most elegant. Here is my solution:

//Sample Data

[Sample]:

LOAD * INLINE

[

Grouping,Fields

Example, ta_tsc_tsc_mcs_hk_atc_gyro_drift_el

Example, ta_tsc_tsc_mcs_hk_atc_gyro_drift_xel

Example, ta_tsc_tsc_mcs_hk_atc_gyro_drift_lo

Album, Now that's what I call Music 7

Album, Now that's what I call Music 8

Album, Now that's what I call Music 9

](delimiter is ',');

//Sub routine to work through each grouping in a table and splitting the common fields from a string

Sub Prefix(Table,Group,Field)

//There are a small number of typos that can impact the splitting - change them all to a space usinging a mapping load

Cleanup:

Mapping Load *

Inline [

AttCode, Attribute

" ", " "

"-", " "

":", " "

];

//Looping through each grouping item in turn

FOR Each v in FieldValueList('$(Group)')

//Create a temp table that just has one single group item present

Temp:

Load Distinct

$(Group) as g,

MapSubString('Cleanup', $(Field)) as f

Resident $(Table)

where $(Group)='$(v)';

//Calculate the number of fields within the Grouping - used to understand when we have a 100% match

LET vRows=NoOfRows('Temp');

//When working through the string character by character, we need to stop when the shortest string length has been consummed

Len:

Load

g,

Min(Len(f)) as l

Resident Temp

Group By g;

//Assign the length of the shortest string to a variable

LET vLen = num(Peek('l',0,'Len'));

Drop Table Len;

//Slowly add an new character from the left until there is no longer a 100% match or the shortest string length as been reached

Set a=1;

Set vCheck=1;

Do while vCheck=1 and a<=($(vLen)+1)

//Return the text value of the shortest string, or the first shortest value if there is multiple values the same length

Short:

Load Distinct

FirstValue(f) as s

Resident Temp

where Len(f)=num($(vLen));

//Assign the shortest text value to a variable

LET vMatch = text(Peek('s',0,'Short'));

Drop table Short;

//With each interation the slowly collect another character from the left

Let vPart = left('$(vMatch)',$(a));

//Count the number of fields that match the current interations left string

Match:

Load

g,

count(f) as p

Resident Temp

where WildMatch(f,'$(vPart)*')>0

Group By g;

//If the left interation count matches the groups row count then we know that there is a 100% match

LET vCheck = if(num(Peek('p',0,'Match'))=$(vRows),1,0);

Drop Table Match;

//Move onto the next character

Let a=a+1;

Loop;

//Due to the way the loop finishes with the first false value, the final counters need to be wound back slightly

Let a = a-2;

Let vPart=left('$(vMatch)',$(a));

//Creating a new output table which has the original field split into two sections

Output:

Load Distinct

$(Group),

left($(Field),$(a)) as $(Field)1,

right($(Field),len($(Field))-$(a)) as $(Field)2

Resident $(Table)

where $(Group)='$(v)';

Drop Table Temp;

//Move onto the next table grouping

NEXT v

//Drop the original data table

Drop Table $(Table);

//Rename the output table to the same name as the original

Rename Table Output to $(Table);

End Sub

Call Prefix('Sample','Grouping','Fields');

2 Replies

anthonyj

Creator III

2022-03-14
07:34 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi @HonestToad ,

I'm sorry I haven't used MATLAB and I'm not sure if this is overly simplistic for your requirements, but based on the example you've provided and assuming these are values in a column then you can use this function to return the nth string value based on the underscores as separators.

subfield([Column Name], '_', 9 )

If there are further complexities to your requirements, let us know and I'm sure we can work them out.

Regards

Anthony

295 Views

HonestToad

Partner - Contributor II

2022-03-17
09:24 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Having thought about it all weekend and bouncing ideas off a colleague, I have come up with a solution that works. It may not be the most elegant. Here is my solution:

//Sample Data

[Sample]:

LOAD * INLINE

[

Grouping,Fields

Example, ta_tsc_tsc_mcs_hk_atc_gyro_drift_el

Example, ta_tsc_tsc_mcs_hk_atc_gyro_drift_xel

Example, ta_tsc_tsc_mcs_hk_atc_gyro_drift_lo

Album, Now that's what I call Music 7

Album, Now that's what I call Music 8

Album, Now that's what I call Music 9

](delimiter is ',');

//Sub routine to work through each grouping in a table and splitting the common fields from a string

Sub Prefix(Table,Group,Field)

//There are a small number of typos that can impact the splitting - change them all to a space usinging a mapping load

Cleanup:

Mapping Load *

Inline [

AttCode, Attribute

" ", " "

"-", " "

":", " "

];

//Looping through each grouping item in turn

FOR Each v in FieldValueList('$(Group)')

//Create a temp table that just has one single group item present

Temp:

Load Distinct

$(Group) as g,

MapSubString('Cleanup', $(Field)) as f

Resident $(Table)

where $(Group)='$(v)';

//Calculate the number of fields within the Grouping - used to understand when we have a 100% match

LET vRows=NoOfRows('Temp');

//When working through the string character by character, we need to stop when the shortest string length has been consummed

Len:

Load

g,

Min(Len(f)) as l

Resident Temp

Group By g;

//Assign the length of the shortest string to a variable

LET vLen = num(Peek('l',0,'Len'));

Drop Table Len;

//Slowly add an new character from the left until there is no longer a 100% match or the shortest string length as been reached

Set a=1;

Set vCheck=1;

Do while vCheck=1 and a<=($(vLen)+1)

//Return the text value of the shortest string, or the first shortest value if there is multiple values the same length

Short:

Load Distinct

FirstValue(f) as s

Resident Temp

where Len(f)=num($(vLen));

//Assign the shortest text value to a variable

LET vMatch = text(Peek('s',0,'Short'));

Drop table Short;

//With each interation the slowly collect another character from the left

Let vPart = left('$(vMatch)',$(a));

//Count the number of fields that match the current interations left string

Match:

Load

g,

count(f) as p

Resident Temp

where WildMatch(f,'$(vPart)*')>0

Group By g;

//If the left interation count matches the groups row count then we know that there is a 100% match

LET vCheck = if(num(Peek('p',0,'Match'))=$(vRows),1,0);

Drop Table Match;

//Move onto the next character

Let a=a+1;

Loop;

//Due to the way the loop finishes with the first false value, the final counters need to be wound back slightly

Let a = a-2;

Let vPart=left('$(vMatch)',$(a));

//Creating a new output table which has the original field split into two sections

Output:

Load Distinct

$(Group),

left($(Field),$(a)) as $(Field)1,

right($(Field),len($(Field))-$(a)) as $(Field)2

Resident $(Table)

where $(Group)='$(v)';

Drop Table Temp;

//Move onto the next table grouping

NEXT v

//Drop the original data table

Drop Table $(Table);

//Rename the output table to the same name as the original

Rename Table Output to $(Table);

End Sub

Call Prefix('Sample','Grouping','Fields');

Community Browser