Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have a table "Contact" which I created in Transformation layer, include data fields "Company_ID", "Company_Name", "Country_ID", "Country_Name". Some records start with character "Work" in this table don't have "Country_ID" and "Country_Name" originally. Here the example I attached only for presenting.
Company_ID | Company_Name | Country_ID | Country |
10001 | WorkShop | ||
10002 | CompanyB | 10 | France |
10003 | CompanyC | 20 | Italy |
10004 | CompanyD | 30 | China |
10005 | CompanyE | 40 | Japan |
10006 | WorkStation |
However, I want to give a "Country_ID" -- ("50") and a "Country_Name" -- ("Gemany") value to these records start with character "Work". Therefore, I added the code as below, but it doesn't work.
Load
Company_ID,
Company_Name,
if (WildMatch(Company_Name,'Work*')=1, Country_ID ='50',Country_ID) as Country_ID,
if (WildMatch(Company_Name,'Work*')=1, Country ='Germany',Country) as Country
From ...
I also tried to use the code below, but it doesn't work as well.
if (Company_Name = 'Work*,Country ='Germany',Country) as Country
Can you please take a look my case and let me know what I can do here to get the ideal result?
Thanks so much,
Becky
Hello,
Please try this
LOAD Company_ID,
Company_Name,
// Country_ID,
//Country,
if(len(Country_ID)=0 and Company_Name like 'work*',50,Country_ID) AS Country_ID,
if(len(Country_ID)=0 and Company_Name like 'work*','Germany',Country) AS Country
FROM
[https://community.qlik.com/thread/225682]
(html, codepage is 1252, embedded labels, table is @1);
or
Load *,
if(len(Country_ID)=0 and Company_Name like 'work*',50,Country_ID) AS CountryID1,
if(len(Country_ID)=0 and Company_Name like 'work*','Germany',Country) AS Country1;
LOAD Company_ID,
Company_Name,
Country_ID,
Country
FROM
[https://community.qlik.com/thread/225682]
(html, codepage is 1252, embedded labels, table is @1);
if (Company_Name like 'Work*' and Country ='Germany', Country) as Country,
you can use too right, left, mid and etc
HI.
If it is was me and always work*
IF(LEFT(Company_Name,4) ='Work',
'50',
Country_ID) AS Country_ID
IF(LEFT(Company_Name,4) ='Work',
'Germany',
Country) AS Country
Mark
Hello,
Please try this
LOAD Company_ID,
Company_Name,
// Country_ID,
//Country,
if(len(Country_ID)=0 and Company_Name like 'work*',50,Country_ID) AS Country_ID,
if(len(Country_ID)=0 and Company_Name like 'work*','Germany',Country) AS Country
FROM
[https://community.qlik.com/thread/225682]
(html, codepage is 1252, embedded labels, table is @1);
or
Load *,
if(len(Country_ID)=0 and Company_Name like 'work*',50,Country_ID) AS CountryID1,
if(len(Country_ID)=0 and Company_Name like 'work*','Germany',Country) AS Country1;
LOAD Company_ID,
Company_Name,
Country_ID,
Country
FROM
[https://community.qlik.com/thread/225682]
(html, codepage is 1252, embedded labels, table is @1);
Hi Becky,
I think a mapping table will help you here. Somewhere in your transformation layer script create a mapping table like this:
MappingCountryID:
Mapping
LOAD
Company_ID,
Country_ID
Resident ..... Where Not Isnull(CountryID)
Now add the CountryId with a left join using this mapping table. Something like
Left Join(Company)
Load
Distinct
Company_ID,
ApplyMap('MappingCountryID',Company_ID,50) as CountryID
Resident Company;
The ApplyMap line has a optional parameter, 50 in this case, that will map to records in the company table where there is no matching Company_ID in the mapping table.
Do something similar for the other field, 'Country', and you should be okay.
Regards
Andrew
Your script is seems good, the only issue I see is the way you are trying to declare the value
Load
Company_ID,
Company_Name,
if (WildMatch(Company_Name,'Work*')=1, '50',Country_ID) as Country_ID,
if (WildMatch(Company_Name,'Work*')=1, 'Germany',Country) as Country
From ...
Thanks so much. It works.
Hi,
if (WildMatch(Company_Name,'Work*')=1, '50',Country_ID) as Country_ID,
if (WildMatch(Company_Name,'Work*')=1, 'Germany',Country) as Country
Thank you so much for your response Ramon. It works! However I'd marked shree909 as correct answer ealier before I saw your post. I will mark yours as helpful!
Best
Becky
Thank you so much for responsing. I found an easy solution. But I also marked yours as helpful. Will keep this technical in my mind!
Best,
Becky