Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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];
You can do another RESIDENT LOAD of your table and fill in values where missing.
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
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];
Tx Phaneedra, !!!!
Bingo <nonconcatenate> did a trick
M
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'.
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 .
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.
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?