Applymap with 2 fields not working

[sifatnabil]

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?

[jagan]

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.

[sifatnabil]

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.

[jagan]

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.

[sifatnabil]

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?

[jagan]

Hi,

If possible attach some sample data, it is difficult to tell.

Regards,

Jagan.

[sifatnabil]

Please find attached. It seems to work here but not with the SQL databases!