Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

update result only at inner join condition?

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.

1 Solution

Accepted Solutions
Not applicable
Author

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

View solution in original post

8 Replies
Not applicable
Author

Hi Michael,

I couldn't understand..

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

Karthik

Not applicable
Author

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

IDClassDeptIndicator



-
AC1MKTGood
BC2HRBad
CC1a
Good
DC2a
Good

SEE ATTACHEMENT

Not applicable
Author

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

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

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

Not applicable
Author

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. 

Not applicable
Author

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;

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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