topic Re: Map field to other table in QlikView

Map field to other table

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

Hi Community,

I want to map field one table to another table.

please look at here..

Suppose i have 2 tables..

Table1:

CostCentre	Account	ClientCode	Amount	Year
5110	170110	366001	1245	2014
1915	110311	ABCD	1000	2014
1915	110711	ABCD	12	2014
1915	110311	ABCD	123	2014
1915	110311	ABCD	45	2014
1915	110311	ABCD	80	2014
1915	110711	ABCD	30	2014

Table2:

CostCentre	ClientCode	Type	Amount	year
5110	366001	WIP IN	1245	2014
1915	ABCD	WIP OUT	1000	2014
1915	ABCD	Notes	12	2014
1915	ABCD	ABC	123	2014
1915	ABCD	SUMAC	0	2014
1915	ABCD	WIP IN	89	2014
1915	ABCD	WIP OUT	9	2014
1916	ABVF	ABVH	19	2014

These are my 2 tables, Table1 is main data, Table2 is also almost same,

but Amount different.

Here i have to map Type field to Table1, i don't bother about Amount in second table.

I want to Table1 Amount only.

Note: Only i have to Map Type field to Table1.

My Output should be, If i am take

Dimension: Type

Expression: Sum({<Year={'2014'}>}Amount)

Type	2014
WIP IN	1325
WIP OUT	1030
Notes	12
ABC	123
SUMAC	45

Hope you Understand....

Thanks in Advance..

Map field to other table

Re: Map field to other table

Kushal_Chawda — Thu, 02 Jun 2016 09:26:40 GMT

Simply link you tables by creating Key like this

Table1:

LOAD ID,

autonumber(Client&Category&Amount) as Key

Client,

Category,

Product,

Amount

FROM table1;

Table2:

LOAD autonumber(Client&Category&Amount) as Key,

Order

FROM table2;

Re: Map field to other table

avinashelite — Thu, 02 Jun 2016 09:36:50 GMT

if you have multiple mapping values like

ID	Client	Category	Product	Amount
1	AC001	Poker Paul	Nokia	1290
2	AC001	Poker Paul	Nokia	-1290

Don't go with the Mapping function simple create a common key field between tables

Table1:

LOAD ID,

Client&'-'&Category&'-'&Amount as Key

Client,

Category,

Product,

Amount

FROM table1;

Table2:

LOAD Client&'-'&Category&'-'&Amount as Key,

Order

FROM table2;

Re: Map field to other table

Nagaraju_KCS — Thu, 02 Jun 2016 17:20:34 GMT

HI Kaushal/Avinash,

please look at here..

Suppose i have 2 tables..

Table1:

CostCentre	Account	ClientCode	Amount	Year
5110	170110	366001	1245	2014
1915	110311	ABCD	1000	2014
1915	110711	ABCD	12	2014
1915	110311	ABCD	123	2014
1915	110311	ABCD	45	2014
1915	110311	ABCD	80	2014
1915	110711	ABCD	30	2014

Table2:

CostCentre	Account	ClientCode	Type	Amount	year
5110	170110	366001	WIP IN	1245	2014
1915	110311	ABCD	WIP OUT	1000	2014
1915	110711	ABCD	Notes	12	2014
1915	110311	ABCD	ABC	123	2014
1915	110311	ABCD	SUMAC	0	2014
1915	110311	ABCD	WIP IN	89	2014
1915	110711	ABCD	WIP OUT	9	2014

These are my 2 tables, Table1 is main data, Table2 is also almost same,

but Amount different.

Here i have to map Type field to Table1, i don't bother about Amount in second table.

I want to Table1 Amount only.

Note: Only i have to Map Type field to Table1.

My Output should be, If i am take

Dimension: Type

Expression: Sum({<Year={'2014'}>}Amount)

Type	2014
WIP IN	1325
WIP OUT	1030
Notes	12
ABC	123
SUMAC	45

Hope you Understand....

Thanks in Advance..

Re: Map field to other table

Nagaraju_KCS — Fri, 03 Jun 2016 04:08:04 GMT

Please guys have a look above post...

Re: Map field to other table

MindaugasBacius — Fri, 03 Jun 2016 04:19:37 GMT

map_type:
mapping LOAD autonumber(CostCentre & ClientCode & Amount & year),
     Type
FROM

(ooxml, embedded labels, table is Sheet2);


Master:
LOAD CostCentre, 
     Account, 
     ClientCode, 
     Amount, 
     Year,
     ApplyMap('map_type', autonumber(CostCentre & ClientCode & Amount & Year), ':') as Type
FROM

(ooxml, embedded labels, table is Sheet1);

The outcome would be like this:

Take a look at the attachment.

Re: Map field to other table

Nagaraju_KCS — Fri, 03 Jun 2016 04:29:04 GMT

But here i don't have Amount same in 2 tables.

I want to fetch from Amount Table1.

My Output should be

Dimension: Type

Expression: Sum({<Year={'2014'}>}Amount)

Type	2014
WIP IN	1325
WIP OUT	1030
Notes	12
ABC	123
SUMAC	45

Re: Map field to other table

MindaugasBacius — Fri, 03 Jun 2016 05:17:21 GMT

Maybe I misunderstood something:

Here i have to map Type field to Table1, i don't bother about Amount in second table.

I want to Table1Amount only.

What are the conditions again please?

Re: Map field to other table

Fri, 03 Jun 2016 05:19:47 GMT

See the attached,

Re: Map field to other table

Nagaraju_KCS — Fri, 03 Jun 2016 05:26:21 GMT

in my second table Amount someone entered manually,

So values are not correct.

Only i need to fetch Table1 Amount Only.

i want to compare with Costcentre, CLientCode and Year.

I have to Map Type field to Table1.

Re: Map field to other table

avinashelite — Fri, 03 Jun 2016 05:33:18 GMT

Try like this

Type_Mapping:

Mapping LOAD

CostCentre&'-'&ClientCode&'-'&Year as Key,

Type

from table2;

Table1:

LOAD

CostCentre,

Account,

ClientCode,

Amount,

Year,

applymap('Type_Mapping',CostCentre&'-'&ClientCode&'-'&Year,'NA') as Type

from

table1;

Re: Map field to other table

Nagaraju_KCS — Fri, 03 Jun 2016 05:33:44 GMT

this is correct, but if i have any shuffle data..

it will wrong.

Re: Map field to other table

avinashelite — Fri, 03 Jun 2016 05:36:26 GMT

Did you tried my suggestion

Re: Map field to other table

Fri, 03 Jun 2016 05:37:07 GMT

Hi,

May be like this,

Create a key With combination of rec no in both tables, then do applemap,

tt:

Mapping LOAD

//CostCentre,

CostCentre&ClientCode&year&RecNo() as key,

Type

Resident Table2;

DROP Table Table2;

map:

LOAD *,

RecNo(),

ApplyMap('tt',CostCentre&ClientCode&Year&RecNo(),'') as type

Resident Table1;

DROP Table Table1;

You can use order by in both table if you have suffle data.

May be this will work try it

Re: Map field to other table

jonathandienst — Fri, 03 Jun 2016 06:12:39 GMT

qv_testing wrote:

in my second table Amount someone entered manually,

So values are not correct.

Only i need to fetch Table1 Amount Only.

i want to compare with Costcentre, CLientCode and Year.

I have to Map Type field to Table1.

You don't have enough information to do what you ask. Costcentre, ClientCode and Year do not identify individual rows in either table, so you cannot associate a single Type with each row in Table1. And you will not be able to produce your output table without some additional information.