Add Assigned Value to Null Value Records

[#1]

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

[#2]

if (Company_Name like 'Work*' and Country ='Germany', Country) as Country,

you can use too right, left, mid and etc

[Mark_Little]

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

[shree909 · Accepted solution]

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);

[effinty2112]

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

[ramoncova06]

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 ...

[#7]

Thanks so much. It works.

[vcanale]

Hi,

if (WildMatch(Company_Name,'Work*')=1, '50',Country_ID) as Country_ID,

if (WildMatch(Company_Name,'Work*')=1, 'Germany',Country) as Country

[#9]

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

[#10]

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