Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a mapping table like this:
MarketID | CustNo | Mcode | Mname |
7 | 643* | 2314 | ABDD |
5 | 643* | 2352 | EWTG |
4 | 45674 | 2643 | AFJK |
7 | 4554 | 3436 | GGGE |
*Note: CustNo is only unique per MarketID; Mcode is totally unique however.
In my main table, I have CustNo and MarketID, and I wish to use applymap to get the Mname
I used:
MAPPING LOAD
MarketID & ':' & CustNo as ID,
Mname
Resident mappingtable;
However this is not working. I tested that the mapping table created returns more than 1 row for each ID:
Why does this happen?
Hi,
Try like this
MAPPING LOAD
MarketID & ':' & CustNo as ID,
MaxSTring(Mname) AS Mname
Resident mappingtable
Group By MarketID & ':' & CustNo;
or you can use MinString(), but this will pull one value among many values, it is similar to Max(). Hope this helps you.
Regards,
Jagan.
Hi,
This is still not working. FYI the mappingtable is a joined table from 2 sources, might that be the issue?
mappingtable:
LOAD
CUSTNO,
MISCODE,
MARKETID;
SQL SELECT
CUSTNO,
MISCODE,
MARKETID
FROM
customer.CUST_TBL;
join
LOAD
MCODE as MISCODE,
MNAME;
SQL SELECT
MCODE,
MNAME
FROM
customer.MISC_TBL;
^I am loading the mapping load from here.
Hi,
Try like this
Temp:
LOAD
MARKETID & '_' & CUSTNO AS Key,
MISCODE;
SQL SELECT
CUSTNO,
MISCODE,
MARKETID
FROM
customer.CUST_TBL;
join
LOAD
MCODE as MISCODE,
MNAME;
SQL SELECT
MCODE,
MNAME
FROM
customer.MISC_TBL;
DROP TABLE Temp;
MappingTableName:
MAPPING LOAD
Key,
MaxSTring(MNAME) AS Mname
Resident Temp
Group By Key;
Hope this helps you.
Regards,
jagan.
Thanks Jagan, but this is still not working - it's giving the incorrect mappings like before. Is there any other way to achieve the correct mapping?
Hi,
If possible attach some sample data, it is difficult to tell.
Regards,
Jagan.
Please find attached. It seems to work here but not with the SQL databases!