Case 893 How to make "-" change to Y ?

[paulyeo11]

Hi All

Below load script work fine :-

VTIGER:
LOAD
*,
COY_NAME as KeyAcc_CoyName
INLINE [
COY_NAME,LAST_NAME,Email
ABB,POW,0rts@stee.stengg.com
XYZ,STEVEN,
];

ZOHO:
LOAD
*,
IF(EXISTS(KeyAcc_CoyName,COY_NAME), 'Y', 'N') as flag_key_acc // need to modify this load script 
INLINE [
COY_NAME,LAST_NAME,Email

ABB,POW,0rts@stee.stengg.com
ADD,WEE,0rts@stee.stengg.com
AAA,KIM
AAA,RAT,catherine@mattenplant.com.sg
];

DROP FIELD KeyAcc_CoyName;

Below is the result i get :-

COY_NAME....flag_key_acc

ABB......... Y
ADD......... N
XYZ......... -

May i know how to make the "-" display as Y ? and Y display as N or -

Paul Yeo

[rubenmarin · Accepted solution]

Hi, with the logic applied, to show Y in ZYX this value needs to be loaded in the ZOHO table, attached a sample on how ths can be done.

[paulyeo11]

Dear Rubenmarin

Thank you for your sharing . 

May i know can you explain to you how you able to logic out ? So that i am able to understand. As this need to be able to analyse 3 step ahead.

Paul

[rubenmarin]

Hello Paul, the code was looking for values in table ZOHO that already existed in table VTIGER, but if the value didn't existed in ZOHO table it was not checked.

First I'm creating a temporal table that concatenates the values of ZOHO and VTIGER, so this table that has all keys to check which ones already existed in VTIGER, this is the tmpZOHO table. It has the full ZOHO table and concatenates the values of VTIGER that doesn't exists in ZOHO using "chkCOY_NAME" field and "where not Exists(chkCOY_NAME, COY_NAME)" clause.

[paulyeo11]

Hi sir

After reading your logic , now more confident in trying. 

Thank you

Paul

[paulyeo11]

Hi Sir

I try to apply your script to my actual application , now i get stuck , hope you can advise me.

https://community.qlik.com/t5/QlikView-Scripting/Case-899-How-to-Flag-2-company-from-database/td-p/1...

Paul