map

[raadwiptec]

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);

[swuehl]

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

[qlikview979]

Hi,

Please share your Qvw file.

Regards,

Mahesh

[qlikview979 · Accepted solution]

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);

[raadwiptec]

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

[swuehl]

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.

[raadwiptec]

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);

[swuehl]

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.

[engishfaque]

Dear Raadwiptec,

Kindly find attached App.

Kind regards,

Ishfaque Ahmed

[#10]

Try this instead of your expression

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