topic Re: Mapping Load - Use the LAST Match in QlikView

Mapping Load - Use the LAST Match

gerhardl — Thu, 04 Jun 2015 08:29:22 GMT

From what I can tell a Mapping Load works the same way as an Excel vLookup, where the ApplyMap used in the table will always find the first available match and then disregard any other matches.

I need it to use the very last match in the mapping table. How can I achieve that?

I think I need to sort the mapping table - I can do this using a field called [Decision Date] or I can use RowNo() and sort it descending - but I;m not sure how to do this - can someone please assist? Is there an opposite to First Sorted Value?

My current script looks something like this:

MapOrigin:

Mapping Load

[Appl No],[Appl Origin Code]

From C:\Users\tenba1\Documents\QlikView\AppsQVD.qvd (qvd); // this I need to sort from bottom to top using date or row

Table:

ApplyMap('MapOrigin', [Appl no], 'Branch App') As [Appl Origin Code]

Re: Mapping Load - Use the LAST Match

hubertdudek — Thu, 04 Jun 2015 09:12:29 GMT

You can just load AppsQVD.qvd as normal table - manipulate it (sort etc) then load it as

mapping load

...

resident tablename

then

drop table tablename

Re: Mapping Load - Use the LAST Match

Thu, 04 Jun 2015 09:20:17 GMT

Hi Gerhard

What i would do is to create first a table Tmp

Tmp_Origin:

Load

[Appl No],[Appl Origin Code]

From C:\Users\tenba1\Documents\QlikView\AppsQVD.qvd (qvd)

order by date desc;

MapOrigin:

Mapping Load

[Appl No],[Appl Origin Code]

resident Tmp_Origin

where not exists ([Appl No]); // will disregard every record if [Appl No] exists already in the table

drop table Tmp_origin;

Table:

ApplyMap('MapOrigin', [Appl no], 'Branch App') As [Appl Origin Code]

best regards

Chris

Re: Mapping Load - Use the LAST Match

gerhardl — Thu, 04 Jun 2015 09:26:27 GMT

Hi Chris,

I thought that I cannot use an Order By Clause on a "normal" table like from a QVD or file, and that it will only work on a resident table.

I'll try both your and huber's suggestions - I just need to figure out the syntax for his.

Re: Mapping Load - Use the LAST Match

Thu, 04 Jun 2015 09:33:22 GMT

Yes you're right,

you need an additional step to create a tmp table to load, then another one to sort

chris

Re: Mapping Load - Use the LAST Match

gerhardl — Thu, 04 Jun 2015 09:54:13 GMT

Okay I am almost there I think. This is my current script:

Tmp_Origin:

Load

[Appl No],[Appl Origin Code], [Decision Date]

From C:\Users\tenba1\Documents\QlikView\AppsQVD.qvd (qvd) ;

LOAD [Appl No],

if([Appl Origin Code]='SA','SMS App', if([Appl Origin Code]='S1','SMS App (New)',if([Appl Origin Code]='BA','Branch App',if([Appl Origin Code]='WA','Customer Direct',if([Appl Origin Code]='PQ','Pre-Qualified',

if([Appl Origin Code]='PA','Promotional App',if([Appl Origin Code]='P1','POS App',if([Appl Origin Code]='W1','Web App',[Appl Origin Code])))))))) as [Appl Origin Code],

Date(Date#( left( [Decision Date],11), 'DD-MMM-YYYY')) as [Decision Date]

FROM

(txt, codepage is 1252, embedded labels, delimiter is '~', no quotes);

MapOrigin:

Mapping Load

[Appl No],[Appl Origin Code]

resident Tmp_Origin

Order By [Decision Date] desc;

drop table Tmp_origin;

I get the atatched error message that says "Table not Found. Drop Tables Statement" - but yet my data now seems to be correct after the load.

Re: Mapping Load - Use the LAST Match

Thu, 04 Jun 2015 09:58:53 GMT

obviously table Statements doesn't exists anymore because :

- already dropped ?

- never created ?

- or has the same structure as a previous table, so records are loaded under an other table name

best regards

Chris

Re: Mapping Load - Use the LAST Match

gerhardl — Thu, 04 Jun 2015 10:41:07 GMT

What is obvious to you may not be as obvious to someone new at QV

- already dropped ? You can see from my script that I haven't dropped it.

- never created ? You can see from the attached image that it loaded and created.

- or has the same structure as a previous table, so records are loaded under an other table name. I'm not sure what this means but from my image you can also see that the second table (MapOrigin) is being created - it's the dropping of the temp table that fails.

That is my entire load script up until the fail, so if there is an obvious error it would be great if you could help point it out.

Re: Mapping Load - Use the LAST Match

sgrice — Thu, 04 Jun 2015 10:58:31 GMT

MapOrigin:

Mapping Load

[Appl No],

firstsortedvalue(distinct [Appl Origin Code],-[Decision Date]) as to

From C:\Users\tenba1\Documents\QlikView\AppsQVD.qvd (qvd)

group by [Appl No]; // this I need to sort from bottom to top

Re: Mapping Load - Use the LAST Match

sgrice — Thu, 04 Jun 2015 11:10:25 GMT

if([Appl Origin Code]='PA','Promotional App',if([Appl Origin Code]='P1','POS App',if([Appl Origin Code]='W1','Web App',[Appl Origin Code])))))))) as [Appl Origin Code],

will simplify to

pick(match([Appl Origin Code],'SA','S1','BA','WA', 'PQ','PA','P1','W1'), 'SMS App','SMS App (New)' ,'Branch App' ,'Customer Direct','Pre-Qualified','Promotional App','POS App' ,'Web App') as [Appl Origin Code],

Re: Mapping Load - Use the LAST Match

maxgro — Thu, 04 Jun 2015 11:15:12 GMT

table name is case sensitive

Tmp_Origin:

load * inline [

];

drop table Tmp_origin;

Re: Mapping Load - Use the LAST Match

gerhardl — Thu, 04 Jun 2015 11:47:30 GMT

Stupid me - thsiw as the problem.

Thanks for everyone who helped.