Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
JonesBeach
Contributor III
Contributor III

Names that almost match should have same information in the remaining columns.

Hi all,

 

Thanks in advance for looking into my problem. 

1. I am trying to look for all the names that starts with "L." and the names that spells exactly the same but without "L." in the start. 

2. I want to align all the values in the remaining columns except for the Date column for all the names that starts with "L." just as the names that spells exactly the same but without "L." in the start. 

Data set:

Names Town City  Article# Date
L.Kumar Singh        
L.Vijelicci Gambino        
L.Tusan Lashkara        
L.De'Roy Johnsson         
Vijelicci Gambino Andrea Islamabad 222 Jun 9 2021
Kumar Singh Geneva  Paris 245 Nov 2 2020
De'Roy Johnsson  Lebanon Glendale 754 Apr 3 2022
Tusan Lashkara Jumerah Sharjah  190 May 1 2020

 

Out put required: 

Names Town City  Article# Date
L.Kumar Singh Andrea Islamabad 222  
L.Vijelicci Gambino Geneva  Paris 245  
L.Tusan Lashkara Lebanon Glendale 754  
L.De'Roy Johnsson  Jumerah Sharjah  190  
Kumar Singh Andrea Islamabad 222 Jun 9 2021
Vijelicci Gambino Geneva  Paris 245 Nov 2 2020
Tusan Lashkara Lebanon Glendale 754 Apr 3 2022
De'Roy Johnsson  Jumerah Sharjah  190 May 1 2020

 

SN: The order of how the names appear does not matter. 

 

Regards,

J. Gabriels 

Labels (1)
1 Solution

Accepted Solutions
edwin
Master II
Master II

you should do this in your script:

data:
load *, if(left(Name,2)='L.', right(Name,len(Name)-2)) as tmp inline [
Name,Town,City ,Article#,Date
L.Not there,,,,
L.Kumar Singh, , , , 
L.Vijelicci Gambino, , , , 
L.Tusan Lashkara, , , , 
L.De'Roy Johnsson , , , , 
Vijelicci Gambino,Andrea,Islamabad,222,Jun 9 2021
Kumar Singh,Geneva ,Paris,245,Nov 2 2020
De'Roy Johnsson ,Lebanon,Glendale,754,Apr 3 2022
Tusan Lashkara,Jumerah,Sharjah ,190,May 1 2020
Not Found,HereTown,HereCity,100,1/1/2022
];

left join (data)
load Name as tmp,Town as TownTmp,City as CityTmp ,Article# as ArticleTmp,Date as DateTmp Resident data ;

NoConcatenate
NewData:
load Name, 
if(not isnull(TownTmp), TownTmp, Town) as Town,
if(not isnull(ArticleTmp), ArticleTmp, Article#) as Article#,
if(not isnull(CityTmp), CityTmp, City) as City,
if(not isnull(DateTmp), DateTmp, Date) as Date
resident data;

drop table data;

 

edwin_0-1653492607878.png

 

 

View solution in original post

6 Replies
edwin
Master II
Master II

you should do this in your script:

data:
load *, if(left(Name,2)='L.', right(Name,len(Name)-2)) as tmp inline [
Name,Town,City ,Article#,Date
L.Not there,,,,
L.Kumar Singh, , , , 
L.Vijelicci Gambino, , , , 
L.Tusan Lashkara, , , , 
L.De'Roy Johnsson , , , , 
Vijelicci Gambino,Andrea,Islamabad,222,Jun 9 2021
Kumar Singh,Geneva ,Paris,245,Nov 2 2020
De'Roy Johnsson ,Lebanon,Glendale,754,Apr 3 2022
Tusan Lashkara,Jumerah,Sharjah ,190,May 1 2020
Not Found,HereTown,HereCity,100,1/1/2022
];

left join (data)
load Name as tmp,Town as TownTmp,City as CityTmp ,Article# as ArticleTmp,Date as DateTmp Resident data ;

NoConcatenate
NewData:
load Name, 
if(not isnull(TownTmp), TownTmp, Town) as Town,
if(not isnull(ArticleTmp), ArticleTmp, Article#) as Article#,
if(not isnull(CityTmp), CityTmp, City) as City,
if(not isnull(DateTmp), DateTmp, Date) as Date
resident data;

drop table data;

 

edwin_0-1653492607878.png

 

 

vinieme12
Champion III
Champion III

Ideally you need something generic and not hard coded to always look for 'L' only 

 

Main:

Load 

Name 

,IF(Subfield(Name,'.',2),Subfield(Name,'.',2),Name) as Key 

From dataset; 

 

Left join(Main)

Load Name as Key, Town,City,Article,Date 

From dataset 

Where Len(Date); 

Drop field Key;

 

 

 

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
JonesBeach
Contributor III
Contributor III
Author

this isnt working. I am making one table by joining two tables in my original data set(which I obviously cant share here) but after doing a left join between the two tables I am creating a new table where I am applying the if statement that you wrote and creating the other two tables one with left join and the other one with NoConcatenate. after that I am making a final table where I am pulling all the fields from NoConcatenate table and joining that table to another which helps me get more data points.

The result I am getting is even worst then before as I am getting no values in for the names with L. in the beginning. 

JonesBeach_0-1653599060382.png

 

 Thanks for the help but could you think of a reason why you were able to get the desired out put and I couldnt?! 

edwin
Master II
Master II

it would be hard to guess what the issue is from your description.  maybe post your script and some test data where the proposed script does not work

JonesBeach
Contributor III
Contributor III
Author

The L.Name was actually in the wrong order I fixed the first and last name order and it worked like a charm.

Thanks much Edwin!

 

edwin
Master II
Master II

np