topic Re: Applymap with 2 fields not working in QlikView

Applymap with 2 fields not working

sifatnabil — Mon, 13 Oct 2014 02:24:42 GMT

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?

Re: Applymap with 2 fields not working

jagan — Mon, 13 Oct 2014 02:49:08 GMT

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.

Re: Applymap with 2 fields not working

sifatnabil — Mon, 13 Oct 2014 04:01:24 GMT

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.

Re: Applymap with 2 fields not working

jagan — Mon, 13 Oct 2014 04:17:05 GMT

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.

Re: Applymap with 2 fields not working

sifatnabil — Mon, 13 Oct 2014 05:30:17 GMT

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?

Re: Applymap with 2 fields not working

jagan — Mon, 13 Oct 2014 05:40:22 GMT

Hi,

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

Regards,

Jagan.

Re: Re: Applymap with 2 fields not working

sifatnabil — Mon, 13 Oct 2014 06:11:50 GMT

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