topic Re: Need Help in QlikView

Need Help

paulwalker — Fri, 31 Jul 2015 06:43:45 GMT

Hi Community,

I have one requirement..

I have 2 tables

Table1:

ID, Value

100, 2500

200, 3000

300, 3500

Table2:

ID, Value

100, 1000

200, 5000

300, 2500,

400, 4500

500, 2000

Here, ID values 100, 200, 300 are same but values are different..

I want to replace the Values in Table2.

My Output should be

Output:

ID, Value

100, 2500

200, 3000

300, 3500,

400, 4500

500, 2000

Thanks in Advance...

Re: Need Help

Ralf-Narfeldt — Fri, 31 Jul 2015 06:48:44 GMT

Table1:

Load * Inline [

ID, Value

100, 2500

200, 3000

300, 3500];

Load * Inline [

ID, Value

100, 1000

200, 5000

300, 2500,

400, 4500

500, 2000] Where Not Exists(ID) ;

Re: Need Help

Anonymous — Fri, 31 Jul 2015 06:49:07 GMT

try this:

Table1:

load ID, Value

from ...

Table2:

noconcatenate load ID, Value

from ...

where not exists (ID)

left join(Table2)

load *

resident Table1

drop Table1;

now your table 2 holds your desired values, maybe you want to sort your table

Re: Need Help

sorrakis01 — Fri, 31 Jul 2015 06:51:21 GMT

Table1:

LOAD * INLINE [

ID, Value

100, 2500

200, 3000

300, 3500

];

JOIN (if you like is not necesary)

Table2:

LOAD * INLINE [

ID, Value

100, 1000

200, 5000

300, 2500,

400, 4500

500, 2000

]

where not exists(ID);

Re: Need Help

pokassov — Fri, 31 Jul 2015 06:53:08 GMT

Hello!

MapTab:

Mapping

load

ID, Value

resident Table1;

Table2:

Load

ID,

ApplyMap('MapTab',ID,Value) As Value

from ...;

Re: Need Help

maxgro — Fri, 31 Jul 2015 06:54:17 GMT

another one, using applymap with 3 parameters, could be

Table1:

mapping load * inline [

ID, Value

100, 2500

200, 3000

300, 3500

];

Table2:

load

ID,

ApplyMap('Table1', ID, Value) as Value

inline [

ID, Value

100, 1000

200, 5000

300, 2500,

400, 4500

500, 2000

];

Re: Need Help

sasiparupudi1 — Fri, 31 Jul 2015 06:57:04 GMT

Table1:

mapping LOAD * Inline

[

ID, Value

100, 2500

200, 3000

300, 3500

];

NoConcatenate

Table2:

load

ID,ApplyMap('Table1',ID,Value) as Value

inline

[

ID, Value

100, 1000

200, 5000

300, 2500

400, 4500

500, 2000

];

Re: Need Help

paulwalker — Fri, 31 Jul 2015 06:58:19 GMT

If i have duplicate ID's,

Is this work ApplyMap perfectly???

Re: Need Help

maxgro — Fri, 31 Jul 2015 07:13:15 GMT

do you mean like this (duplicated in mapping table)? 100 is duplicated

Table1:

load * inline [

ID, Value

100, 2500

200, 3000

300, 3500

100, 999999999

];

mapping load * Resident Table1 order by Value desc;

DROP Table Table1;

Table2:

load

ID,

ApplyMap('Table1', ID, Value) as Value

inline [

ID, Value

100, 1000

200, 5000

300, 2500,

400, 4500

500, 2000

];

I think the applymap uses the first one, in that case 9999..... because of the sort in resident load

and this without sort

Re: Need Help

sebastianlettner — Fri, 31 Jul 2015 07:54:20 GMT

Hi,

here is an example without Mapping

TableA:

LOAD * INLINE

[

ID, Value

100, 2500

200, 3000

300, 3500

];

TableB:

NoConcatenate

LOAD * INLINE

[

ID, Value

100, 1000

200, 5000

300, 3500

400, 4500

500, 2000

];

Outer Join(TableB)

LOAD

ID,

Value as ValueA

Resident TableA;

Output:

NoConcatenate

LOAD

ID,

if(not IsNull(ValueA), ValueA, Value) as Value

Resident TableB;

drop tables TableA, TableB;

Re: Need Help

puttemans — Fri, 31 Jul 2015 07:57:41 GMT

Indeed, mapping selects only one of them, not both. So if you have in your table you're mapping a duplicate value, the mapping process is not optimal.

Re: Need Help

maxgro — Fri, 31 Jul 2015 08:46:01 GMT

with mapping if you have n records in Table2

you get the same number of record in output table

the requirement was to replace values in Table2, not to add records in Table2, so I think mapping works

Re: Need Help

qlikviewwizard — Fri, 31 Jul 2015 09:11:57 GMT

Please use the below script:

Table1:

LOAD * INLINE [

ID, Value

100, 2500

200, 3000

300, 3500

];

Table2:

LOAD * INLINE [

ID, Value

100, 1000

200, 5000

300, 2500,

400, 4500

500, 2000]

Where Not Exists(ID)

Re: Need Help

paulwalker — Fri, 31 Jul 2015 10:57:53 GMT

Thanks For reply EveryOne..

Table1:

ID	Customer
100	Andy
101	Sandy
102	Luria
103	Paul
104	Ruzia

Table2:

ID	Customer Name	Value
100	Andy	20000
	Andy	2300
111	Andy	3400
200	Ruzia	1200
101	Sandy	2300
103	Paul	2300
123	Paul	2500
105	Kanna	12000

I have 2 tables, I want map ID to ID and Customer to Customer.

Suppose i have 2 List boxes 1. Customer and Customer Name

and TextBox= Sum(Value)

If i am click Customer and Customer Name

It should be Andy= 22300, because here compare ID to ID and Customer to Customer.

Suppose Kanna= 12000 and paul=2300

Hope You Understand.......

Re: Need Help

paulwalker — Fri, 31 Jul 2015 11:28:57 GMT

This is only for example,

but my source data many of Null (ID and Customer both)

Re: Need Help

Ralf-Narfeldt — Fri, 31 Jul 2015 11:50:14 GMT

I fail to see the use of Table 1, because the information is already in Table2.

Why do you need to link them?

Are Andy with ID100 the same as Andy with ID 111?

Re: Need Help

paulwalker — Fri, 31 Jul 2015 12:02:39 GMT

Yeah Ralf,

My data like that only..

I have most of the null values, even ID's and Customer Also....

I want to get values from Table2, If i don't have any Common ID's also i want to get the data..