Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
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;
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.
Thanks for the help but could you think of a reason why you were able to get the desired out put and I couldnt?!
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
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!
np