Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Replace Null to 'NA' in Jone'ed Load

Hi,

I  have data load like below :

[Detals]:

LOAD

   Dept,

   Sub    as [CatID]

from [lib://myfile.xlsx];

left join            //------------------

[MapCat]:

LOAD

    //CategoryName as Category,

    if(IsNUll(CategoryName), 'Other', CategoryName) as Category,

    text(Sub)  as [CatID]

FROM [lib://mymapfile.xlsx];

My goal to have all missing Category = 'Other' , I tried this syntax but it didn't work, I feel that that LOAD works bit differently then SQL join.

and I want to keep my left join in place. Is there the way to achive this in Q?

CatID      Category

11          Alpha

22          Bravo

3333        Other**??

Thanks

M

1 Solution

Accepted Solutions
phaneendra_kunc
Partner - Specialist III
Partner - Specialist III

what Stefan said is correct. You have to perform NULL check once the data is left joinied. Until then you dont have a missing items. TRY BELOW

[Detals]:

LOAD

   Dept,

   Sub    as [CatID]

from [lib://myfile.xlsx];

left join           

[MapCat]:

LOAD

    CategoryName as Category,

    text(Sub)  as [CatID]

FROM [lib://mymapfile.xlsx];

[Detals_NEW]:

NoConcatenate

Load

Dept,

[CatID],

if(IsNUll(Category), 'Other', Category) as Category

Resident [Detals];

Drop table [Detals];

View solution in original post

8 Replies
swuehl
MVP
MVP

You can do another RESIDENT LOAD of your table and fill in values where missing.

Not applicable
Author

Tx swuehl, I've checked all info, but looks like it's not intended to work with join

in case like below, result of this join goes into 2 tables(?), I played with resident and nothing worked,

Bit confused. How to make resident load for single table which is result of Join:

[Details_T] :

LOAD * inline [ CatID, Amount

111, 1000

999, 999 ];

         left join

[Map_T]:

LOAD * inline [CatID, CatName

111, 'Alpha'

333, 'Charlie' ];


Details:

Load CatID,

if(IsNUll(CatName), 'Other', CatName) as CatName,      // ???

Amount resident Details_T;

Drop Table Details_T;

Tx

phaneendra_kunc
Partner - Specialist III
Partner - Specialist III

what Stefan said is correct. You have to perform NULL check once the data is left joinied. Until then you dont have a missing items. TRY BELOW

[Detals]:

LOAD

   Dept,

   Sub    as [CatID]

from [lib://myfile.xlsx];

left join           

[MapCat]:

LOAD

    CategoryName as Category,

    text(Sub)  as [CatID]

FROM [lib://mymapfile.xlsx];

[Detals_NEW]:

NoConcatenate

Load

Dept,

[CatID],

if(IsNUll(Category), 'Other', Category) as Category

Resident [Detals];

Drop table [Detals];

Not applicable
Author

Tx Phaneedra, !!!!

Bingo  <nonconcatenate> did a  trick

M

jonathandienst
Partner - Champion III
Partner - Champion III

A left join in QV works the same as a left join in SQL - the unmatched rows get nulls in the joined fields.

Rather than using a join, use ApplyMap:

[MapCat]:

Mapping LOAD

    CategoryName

    Sub

FROM [lib://mymapfile.xlsx];

[Detals]:

LOAD

   Dept,

   Sub    as [CatID],

   ApplyMap('MapCat', Sub, 'Other')

from [lib://myfile.xlsx];

This will replace unmatched Sub values with 'Other'.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
oknotsen
Master III
Master III

If your question is now answered, please flag the Correct Answer (via the big "Correct Answer" button near every post).

If not, please make clear what part of this topic you still need help with .

May you live in interesting times!
Colin-Albert

Consider using a Mapping table and ApplyMap rather than the join.

Applymap will allow you to specify 'N/A' or any other text for unmatched values, and is far faster than joining tables.

If you need to join more fields then you can use several mapping tables and applymap commands.

Don't join - use Applymap instead

Not applicable
Author

Tx all guys,

Tx Phaneedra for your correct answer. 

And Thanks Jonathan for your another correct answer with apply Map.

Sorry if I can't mark them both as such.


In case of resident load do I need to drop all tables_T* used in join? or only first=main one?