Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Fill empty value with mapping

Hi,

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

Header 1Header 2
AYES
BNO
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!

1 Solution

Accepted Solutions
anbu1984
Honored Contributor III

Re: Fill empty value with mapping

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]

17 Replies
datanibbler
Esteemed Contributor

Re: Fill empty value with mapping

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

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

MappingTable:

Mapping LOAD H1,

                         H2

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

Hi,

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

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Not applicable

Re: Fill empty value with mapping

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

Header 1Header 2
AYes
BNo
CYes
DNo
EYes
FNo

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

anbu1984
Honored Contributor III

Re: Fill empty value with mapping

Pass [Header 1] to Applymap

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


Not applicable

Re: Fill empty value with mapping

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]

Gabriel
Valued Contributor II

Re: Fill empty value with mapping

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;

Not applicable

Re: Fill empty value with mapping

Hi,

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

anbu1984
Honored Contributor III

Re: Fill empty value with mapping

Try as sunindia suggested above

Community Browser