topic Re: Fill empty value with mapping in QlikView

Fill empty value with mapping

Mon, 11 May 2015 12:08:18 GMT

Hi,

I want to fill empty values with mapping by an Excel file. The table in Qlikview looks like this:

Header 1	Header 2
A	YES
B	NO
C
D

I have an Excel file with which I can substitute the empty values with YES or NO according to header 1 (in this case according to C and D). How can I do this?

Thank you in advance!

Re: Fill empty value with mapping

datanibbler — Mon, 11 May 2015 12:11:36 GMT

Hi,

you can check by using the LEN() function whether the field [Header2] is filled or not and then act accordingly.

Check first whether that works as expected - it is not always that easy. IF LEN() doesn't work, try with ISTEXT().

HTH

Re: Fill empty value with mapping

sunny_talwar — Mon, 11 May 2015 12:13:23 GMT

You can load your mapping table with a mapping load and use ApplyMap:

MappingTable:

Mapping LOAD H1,

FROM yourExcelFile;

FactTable:

LOAD [Header 1],

If(not IsNull([Header 2], [Header 2], ApplyMap('MappingTable', [Header 2])) as [Header 2]

FROM yourExcelFile;

I hope this will guide towards the right direction.

Best,

Sunny

Re: Fill empty value with mapping

PrashantSangle — Mon, 11 May 2015 12:14:03 GMT

Hi,

Is there any logic you have for which value you are applying for C and which value apply for D???

Regards

Re: Fill empty value with mapping

Mon, 11 May 2015 12:40:24 GMT

Yes, in this case I have an Excel file which looks like this:

Header 1	Header 2
A	Yes
B	No
C	Yes
D	No
E	Yes
F	No

And I want to substitute the empty field rows with this Excel file according to header 1.

Re: Fill empty value with mapping

anbu1984 — Mon, 11 May 2015 12:41:59 GMT

Pass [Header 1] to Applymap

If(not IsNull([Header 2], [Header 2], ApplyMap('MappingTable', [Header 1])) as [Header 2]

Re: Fill empty value with mapping

Mon, 11 May 2015 12:42:28 GMT

Hi,

Thank you for your response, however it doesn't work. It does not recognize it as a correct code within the script.

In my case, I put it under map7.

If(not IsNull([Header 2], [Header 2], ApplyMap('Map7', [Header 2])) as [Header 2]

Re: Fill empty value with mapping

Gabriel — Mon, 11 May 2015 12:43:10 GMT

Hi,

Try this

DataTable:

LOAD [Header 1],

If(IsNull([Header 2] OR LEN([Header 2]) = 0,

ApplyMap('YourMapTableName', [Header 2])

[Header 2] ) AS [Header 2]

FROM YourSource;

Re: Fill empty value with mapping

Mon, 11 May 2015 12:43:57 GMT

Hi,

Thanks for the response. I understand your logic and it is a good alternative, however, I want a more direct solution.

Re: Fill empty value with mapping

anbu1984 — Mon, 11 May 2015 12:45:04 GMT

Try as sunindia suggested above

Re: Fill empty value with mapping

PrashantSangle — Mon, 11 May 2015 12:47:12 GMT

Hi,

Then You can link your QVD datafile with excel file on the basis of Header1,

Try like

File1:

Load Header1,

Header2

from QVDFile;

Left Join

Load Header1,

Header2 as Header_Temp

from ExcelFile;

Final:

Load Header1,

If(isnull(Header2),Header_Temp,Header2) as finalHeader2

Resident File1;

Drop table File1;

Regards

Re: Fill empty value with mapping

Mon, 11 May 2015 12:48:13 GMT

sunindia 's solution is what you have to do

Re: Fill empty value with mapping

anbu1984 — Mon, 11 May 2015 12:48:25 GMT

You should use [Header 1] in ApplyMap(). If [Header 2] is not null, then you can try this

If(Len(Trim([Header 2])) > 0, [Header 2], ApplyMap('Map7', [Header 1])) as [Header 2]

Re: Fill empty value with mapping

Mon, 11 May 2015 12:48:46 GMT

This will work,

though I would maybe suggest just using the len, as that will catch the true nulls and empty string, so no need for the isnull also

YourMapTableName:

Mapping Load

[Header 1]

,[Header 2]

From excel..

DataTable:

LOAD [Header 1],

If(LEN([Header 2]) = 0,

ApplyMap('YourMapTableName', [Header 2])

[Header 2] ) AS [Header 2]

FROM YourSource;

Joe

Re: Fill empty value with mapping

Mon, 11 May 2015 12:50:32 GMT

Hi,

I get the following error:

Error in expression:

IsNull takes 1 parameter

Re: Fill empty value with mapping

Mon, 11 May 2015 12:52:36 GMT

missed closed

If(IsNull([Header 2] ) OR LEN([Header 2]) = 0,

but see my post on the null also

Re: Fill empty value with mapping

Mon, 11 May 2015 12:59:18 GMT

Thank you, your answer worked! Much obliged

Re: Fill empty value with mapping

mohammadkhatimi — Mon, 11 May 2015 13:03:30 GMT

Use [Header 1] in ApplyMap(). If [Header 2] is not null, then you can try this

If(Len(Trim([Header 2])) > 0, [Header 2], ApplyMap('Map7', [Header 1])) as [Header 2]

Regards,

Mohammad