topic Re: Add Assigned Value to Null Value Records in QlikView

Add Assigned Value to Null Value Records

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Add Assigned Value to Null Value Records

Tue, 19 Jul 2016 14:33:23 GMT

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

you can use too right, left, mid and etc

Re: Add Assigned Value to Null Value Records

Mark_Little — Tue, 19 Jul 2016 14:46:10 GMT

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

Re: Add Assigned Value to Null Value Records

shree909 — Tue, 19 Jul 2016 14:46:34 GMT

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

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

Re: Add Assigned Value to Null Value Records

effinty2112 — Tue, 19 Jul 2016 14:51:59 GMT

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

Re: Add Assigned Value to Null Value Records

ramoncova06 — Tue, 19 Jul 2016 15:04:22 GMT

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

Re: Add Assigned Value to Null Value Records

Tue, 19 Jul 2016 15:05:08 GMT

Thanks so much. It works.

Re: Add Assigned Value to Null Value Records

vcanale — Tue, 19 Jul 2016 15:05:29 GMT

Hi,

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

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

Re: Add Assigned Value to Null Value Records

Tue, 19 Jul 2016 16:39:18 GMT

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

Re: Add Assigned Value to Null Value Records

Tue, 19 Jul 2016 16:43:31 GMT

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

Re: Add Assigned Value to Null Value Records

ramoncova06 — Tue, 19 Jul 2016 17:55:41 GMT

glad it worked