topic Re: map in QlikView

map

raadwiptec — Wed, 25 Nov 2020 16:16:04 GMT

hi,

How could i map two different field names but the data inside is mostly similiar

for ex as below

Data1.xls has the following data

Transaction ¦ Account No

22222222222 89776

54545454585 45885

54551854215 96562

Data2.txt

Invoice

22222222222 012

54545454585 011

54551854215 022

Iam trying the following, I dont get any errors.. but in the first try i dont get values,,second try iam getting transaction and not the Account no

[Data1];

Mapping LOAD

Transaction ,

[Account No]

from Data1.xlsx

((ooxml, embedded labels, table is Sheet1);

[Data2];

Load *,

ApplyMap(;Data1',Invoice) as [Account No]

from Data2.txt

(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

Re: map

swuehl — Mon, 23 May 2016 12:02:08 GMT

Have you enclosed the Map table name in single quotes?

ApplyMap('Data1',Invoice) as [Account No]

edit: In addition, I think you should get an error that field [Account No] already exists in Data1 input table.

Re: map

jonathandienst — Mon, 23 May 2016 12:02:16 GMT

I don't know if they are copy and paste errors, or actual errors in your code, but you have an extra opening parenthesis in the ooxml qualifier and a syntax error in the ApplyMap statement.

Re: map

santiago_respane — Mon, 23 May 2016 12:02:46 GMT

Hi,

your code seems fine except for the ";" before Data1 in your applymap sentence and the extra opening parenthesis when reading from Data1 the from sentence.

Should be like this:

[Data2];

Load *,

ApplyMap('Data1',Invoice) as [Account No]

from Data2.txt

(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

Hope this helps.

Kind regards,

Re: map

ramasaisaksoft — Mon, 23 May 2016 12:03:32 GMT

Hi Raadwiptec,

What out put you want at last?

Re: map

qlikview979 — Mon, 23 May 2016 12:09:20 GMT

Hi,

Try like this ,

ApplyMap('Data1',Invoice) as [Account No]

Note:- In your applaymap Statement Remove ";" in front of Data1;

Regardds,

Mahesh.

Re: map

raadwiptec — Mon, 23 May 2016 12:11:07 GMT

hi all,

sorry what ever u mentioned are copy and paste errors.I dont get any errors.on the reload. I want the [Account No] as the output .based on the mapping betweenTransaction and Invoice.

My Mapiping Load is this

[Data1]:

Mapping LOAD

Transaction ,

[Account No]

from ......xxxx

the only difference between transaction and Invoice is .. in invoice i have spaces with additional 012, 011 and 022 etc

22222222222 012

54545454585 011

54551854215 022

For this above i also tried

[Data2];

Load *,

ApplyMap('Data1',Lesft(Invoice,11) as [Account No]

from Data2.txt

(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

Now currently when i reload.. I get the output as

Account No¦

22222222222

54545454585

54551854215

Re: map

swuehl — Mon, 23 May 2016 12:14:28 GMT

Again there seem to be some typos..

and maybe you are trying to map a text against numeric values, try:

ApplyMap('Data1',Num#(trim(Left(Invoice,11))),'no map of' & Invoice) as [Account No]

Re: map

qlikview979 — Mon, 23 May 2016 12:28:55 GMT

Hi,

I hope it is help full for you .

Try like this

T1:

load * Inline [

Transaction , Account No

22222222222 , 89776

54545454585 , 45885

54551854215 , 96562

];

T2:

mapping load Transaction,

[Account No]

Resident T1;

DROP table T1;

T3:

load * Inline [

Invoice

22222222222 012

54545454585 011

54551854215 022

];

T4:

load

ApplyMap('T2',SubField(Invoice,' ',1)) as ABC

Resident T3;

DROP table T3;

Re: map

raadwiptec — Mon, 23 May 2016 13:35:56 GMT

Hi Swehi/Mahes,

I tried both, But it is giving me only output of transaction data and not the Account no.Neither any errors.

Re: map

swuehl — Mon, 23 May 2016 13:37:52 GMT

Please post a small sample QVW or input files to look at.

Re: map

qlikview979 — Tue, 24 May 2016 04:55:49 GMT

Hi,

Please share your Qvw file.

Regards,

Mahesh

Re: map

qlikview979 — Tue, 24 May 2016 06:06:14 GMT

Hi,

I think your data format is different that's why you are not getting correct output.

Please try like this ,

Data1:

mapping

LOAD num([Transaction ]) as [Transaction ],

[Account No]

FROM

(ooxml, embedded labels, table is Sheet3);

Data2:

LOAD Invoice,

SubField(Invoice,' ',1) as sub,

ApplyMap('Data1',num(Trim(Left(Invoice,11)))) as AccountNo1

FROM

(ooxml, embedded labels, table is Sheet4);

Re: map

raadwiptec — Tue, 24 May 2016 08:47:31 GMT

Hi Mahesh,/All

Iam getting few values now.. after using num but noit entire..here some transaction 2 account numbers.

for ex:

transaction ¦ Account number

2343434543 0255554

2343434543 0656566

for the above it is taking only one value and not the other one

Re: map

swuehl — Tue, 24 May 2016 08:58:22 GMT

A Mapping table is like a table of key / value pairs,with unique keys.

So you can't map a key value to two different values, like in your sample. Only the first in load order will be considered.

If you want to create multiple records with different values, you can maybe JOIN the tables.

Re: map

raadwiptec — Tue, 24 May 2016 09:22:28 GMT

so here should i use inner join with 2 columns? would be give me the results.?

Join(Transaction)

[Account No]

From

from Data1.txt

(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

Re: map

swuehl — Tue, 24 May 2016 09:26:53 GMT

If the key values and key field names match, this should then join your Account No to your transaction table,

though potentially duplicate transaction lines.

Not quite sure what you want to achieve here, having multiple accounts per transaction.

In addition, you might run into similar issues as with the mapping approach w.r.t. matching the key values.

Maybe start by just linking both tables and examine the field values and key relation ship using list boxes.

Re: map

engishfaque — Tue, 24 May 2016 15:41:50 GMT

Dear Raadwiptec,

Kindly find attached App.

Kind regards,

Ishfaque Ahmed

Re: map

Tue, 24 May 2016 15:49:07 GMT

Try this instead of your expression

ApplyMap('Data1',Num#(trim(Left(Invoice,11))),'no map of' & Invoice) as [Account No]