Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
How do I conditionally apply a map or join?
I have 2 tables, 1 is my master data which has 2 columns - 1 with an OfficeID and 1 with OfficeName. Sometimes OfficeID is populated and other times OfficeName. I have a second table (link table) that has the list of OfficeID's and corresponding names. I want to apply a map or populate all of the NULL OfficeName records with the information from the second table. What is the best way to accomplish this?
Thank you!
Use this:
if (Trim(OfficeName)='', ApplyMap('Office_Map_by_id', Id),OfficeName) as OfficeName
It will work.
Regards,
Anosh
Hi,
can office names be the same for different ids?
don't know is this the best way, but you can solve it with two mapping tables:
OFFICE:
LOAD * INLINE [
ID, NAME
1, office_1
2, office_2
3, office_3
4, office_4
5, office_5
6, office_6
];
Office_Map_by_id:
Mapping LOAD
ID, NAME Resident OFFICE;
Office_Map_by_name:
Mapping LOAD
NAME, ID Resident OFFICE;
TEST:
LOAD * INLINE [
F1, F2
, office_1
2,
3,
, office_4
, office_5
1,
6,
, office_5
];
TEST_RESULT:
LOAD if (IsNull(F1) or trim(F1)='', ApplyMap('Office_Map_by_name', F2, null()), F1) as result_id,
if(IsNull(F2) or trim(F2)='', ApplyMap('Office_Map_by_id', F1, null()), F2) as result_name
Resident TEST;
regards
If i understood your concept, you can achive this using mapping concept.
For example:
Office:
Load OfficeID, OfficeName from Office:
Map1:
Mapping OfficeID,OfficeName Resident Office;
Office2:
Load *, ApplyMap('Map1',OfficeID,Null()) as OfficeName
From LinkTable;
Hi sspringer,
Use isnull() clause to check where the office name is null and then use applymap to map it with office names as shown below in the load statement:
Load
if(isnull(office name)=-1, applymap())
Hope this help,
Anosh
I'm still not sure this solution works. I tried this if(isnull(office name)=-1, applymap()) , however, if I use this and alias it as OfficeName, then I cannot also load OfficeName in the script above for rows that already have an OfficeName. The problem is this: I have a table with 20 rows, 10 rows already have OfficeName, but no OfficeID (those are fine). 10 rows have OfficeID but no OfficeName - I need to fill in those OfficeName's using a different table.
Any more ideas? Thank you so much for responding!
Use this:
if (Trim(OfficeName)='', ApplyMap('Office_Map_by_id', Id),OfficeName) as OfficeName
It will work.
Regards,
Anosh