topic Re: update result only at inner join condition? in QlikView

update result only at inner join condition?

Fri, 05 Jul 2013 02:34:58 GMT

i have two table : Table A and Table B

Table A structure

ID Class Dept Indicator

A C1 Good

B C2 Bad

C C1a Good

D C2a Good

Table B structure

ID Class

A C1

B C2

C C3

D C4

According to my condition, (department value is update only when Table B.ID= Table A.ID and Table B.Class= Table A.Class and Indicator=Good)

Expect result

Finally table

ID Class Dept Indicator

A C1 MKT Good

B C2 HR Bad

C C1a Good

D C2a Good

So i use left join to join two tables first and using if statement to update value, however, i got the result like as below

actutal result

Finally table

ID Class Dept Indicator

A C1 MKT Good

B C2 HR Bad

C C1a MKT Good

D C2a MKT Good

Should i using inner join first to do transformation first and then concate it back to orginially table? Pls advise.

Re: update result only at inner join condition?

Fri, 05 Jul 2013 05:07:34 GMT

Hi Michael,

I couldn't understand..

From where you are getting values for department? MKT & HR..

Karthik

Re: update result only at inner join condition?

Fri, 05 Jul 2013 05:16:06 GMT

try this

in script write this

TableA:

LOAD * INLINE [

ID, Class, Dept, Indicator

A, C1, MKT, Good

B, C2, HR, Bad

C, C1a, MKT, Good

D, C2a, MKT, Good

];

join

TableB:

LOAD *,Class as Class_new INLINE [

ID, Class

A, C1

B, C2

C, C3

D, C4

];

then in straight table--

Dimension1=ID

dimension2=Class

expression1=if(Class=Class_new, Dept,' ')

expression2=Indicator

THEN OUTPUT LIKE THIS

ID	Class	Dept	Indicator
			-
A	C1	MKT	Good
B	C2	HR	Bad
C	C1a		Good
D	C2a		Good

SEE ATTACHEMENT

Re: update result only at inner join condition?

Fri, 05 Jul 2013 05:16:14 GMT

using if statement and assign value MKT and HR, such as:

if(indicator='Good', 'MKT', '',

if((indicator='Bad, 'HR', '')) as Dept

Re: update result only at inner join condition?

Fri, 05 Jul 2013 05:20:07 GMT

Thx. As the table is not a finalized table to use, i would like the result is handled by scripts instead of expression at this stage.

Re: update result only at inner join condition?

Fri, 05 Jul 2013 05:23:40 GMT

TableA:

LOAD * INLINE [

ID, Class, Dept, Indicator

A, C1, MKT, Good

B, C2, HR, Bad

C, C1a, MKT, Good

D, C2a, MKT, Good

];

join

TableB:

LOAD *,Class as Class_new INLINE [

ID, Class

A, C1

B, C2

C, C3

D, C4

];

LOAD ID, Class, Dept, Indicator,

if(Class=Class_new, Dept,' ') AS dEPT_NEW

Resident TableA;

Re: update result only at inner join condition?

Fri, 05 Jul 2013 07:13:04 GMT

The dept value is dynamic update to finalized table instead of input at inline table

Re: update result only at inner join condition?

Fri, 05 Jul 2013 07:13:33 GMT

The dept value is dynamic update to finalized table instead of input at inline table

Re: update result only at inner join condition?

Fri, 05 Jul 2013 08:45:43 GMT

i found the ansewers for myself and i know how to work.

First, creaet a outer join table

Second, create a temp table to store the Primary key.

Third, resident the outer join table, update column value by using if statment and exists function together.

Then, we can drop the table.

Many thanks all of you to give me advice.

Thank you very much