Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Customers, Partners & Luminaries only: You're invited to a Data Analytics Roadmap session. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Partner
Partner

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
Highlighted
MVP
MVP

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

Highlighted
Not applicable

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

Highlighted
Partner
Partner

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

Highlighted
Not applicable

Tx Phaneedra, !!!!

Bingo  <nonconcatenate> did a  trick

M

Highlighted

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

Don't allow Perfection to be the enemy of Good Enough.
May you live in interesting times!
Highlighted

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

Highlighted
Not applicable

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?