topic Re: Compare two fields and replace certain values in QlikView

Compare two fields and replace certain values

crystles — Wed, 07 Dec 2016 15:28:04 GMT

I have a table with Customers and Customer IDs. The problem is that about 4 or 5 of these Customers are listed in a dataset with the parent Customer and we need to break it up by state and replace the Parent Customer ID with the Child Customer ID based on the state.

Example of Child-Parent relationship

Parent Customer ID	Parent Customer State	Child Customer ID	Child Customer State
1233	Georgia	1534	Georgia
1233	California	1635	California
1233	Maine	1936	Maine

This is only correct for a select number of states, so I created an Excel file that has the ParentID, the State and the New Child ID I want to change the Parent ID to for those specific customers.

I believe I know the way to fix this, but I keep getting weird results.

I create a key in the First table and the Fix Excel document, that shows the ParentID and State. I use that key to join the two tables and compare them, and if the Keys match then replace the key with the new key, if not, then keep the original.

I'm not sure it is doing this in the most efficient way though.

If anyone else has had this issue and has any ideas or tricks that worked for them, any help would be greatly appreciated! Thanks!

Re: Compare two fields and replace certain values

sunny_talwar — Wed, 07 Dec 2016 15:30:10 GMT

Can you share the script/sample to show what you are doing?

Re: Compare two fields and replace certain values

crystles — Wed, 07 Dec 2016 15:46:06 GMT

There is a lot of script that goes before this to get the list of original customers, but here is the section that adds the keys and then compares them.

//*************************************************************************
// add key to main table
//*************************************************************************
SalesTrans2:
LOAD *,
STATE_ABBREV &'-'& CU_PARENTBP as DealerKey_Org
Resident SalesTrans_Units
;

DROP Table SalesTrans_Units;

//*************************************************************************
//Add key from Dealer Fix Excel document
//*************************************************************************

Left Join (SalesTrans2)
LOAD StateName as FIX_StateName,
StateID as FIX_StateID,
DISTRIBUTOR_ID as OLD_DealerID,
DISTRIBUTOR_ID as CU_PARENTBP,
DealerID as NEW_DealerID,
StateID & '-'& DISTRIBUTOR_ID as DealerKey_NewFix
FROM
POLK_MAP_DealerFix.xlsx
(ooxml, embedded labels, table is BossUnits);

//*************************************************************************
//Compare and replace dealers based on the Dealer Fix excel doc
//*************************************************************************
SGM_2:
LOAD *,
if(DealerKey_NewFix = DealerKey_Org, NEW_DealerID, ORG_DealerID) as DealerID_FINAL,
if(DealerKey_NewFix = DealerKey_Org, DealerKey_NewFix, DealerKey_Org) as DealerKey_FINAL,
if(DealerKey_NewFix = DealerKey_Org, FIX_StateID, STATE_ABBREV) as StateID_FINAL

Resident SalesTrans2;

DROP Table SalesTrans2;

Re: Compare two fields and replace certain values

MarcoWedel — Mon, 12 Dec 2016 00:10:23 GMT

Can you describe your expected result given your provided sample data?

thanks

regards

Marco

Re: Compare two fields and replace certain values

crystles — Wed, 14 Dec 2016 14:50:14 GMT

I found out what the issue was. I'll try to explain as clearly as I can, in case someone else has this issue and comes across this post.

In my code I had a list of Dealers, all the Parent Dealers. But for about 5 Dealers, who showed up with different Dealer States but the same DealerID, we needed to break these out and use the Child DealerID instead to be able to differentiate between them. To do this, I have an Excel document that I add a new field that only relates to the Full list by these 5 dealers.

So essentially I have a field with the full list of dealers, and I add a field with only the 5 dealers I need changed, by a key that connects them to the specific 5 dealers, and the rest of the records in that field are blank. This is where my problem was, because my formula did not address all the blank records in my new field.

I changed my formula. Instead of looking for where the new field matched one of the DealerIDs in my full field, I just checked when the record was blank. If the record is blank, use the Original DealerID, if the field is NOT blank, then use the new DealerID provided in the new field. This fixed my issues with the data.

Below is the final script I used in my document. Thanks!

//*************************************************************************

// add key to main table now that state and customer have been joined/added

//*************************************************************************

SalesTrans2:

LOAD *,

STATE_ABBREV &'-'& CU_PARENTBP as DealerKey_Org

Resident SalesTrans_Units

;

DROP Table SalesTrans_Units;

//*************************************************************************

//Add key from Dealer Fix Excel document

//*************************************************************************

Left Join (SalesTrans2)

LOAD

// StateName as FIX_StateName,

// StateID as FIX_StateID,

// DISTRIBUTOR_ID as OLD_DealerID,

// DealerID as NEW_DealerID,

StateID & '-'& DISTRIBUTOR_ID as DealerKey_Org,

StateID &'-'& DealerID as DealerKey_NewFix

FROM

[..\..\01. Repository\99. External\data\POLK_MAP_DealerFix.xlsx]

(ooxml, embedded labels, table is BossUnits);

DROP Fields CU_PARENTBP, STATE_ABBREV;

//*************************************************************************

//Compare and replace dealers based on the Dealer Fix excel doc

//*************************************************************************

SGM_2:

LOAD *,

if(Len(trim(DealerKey_NewFix)) = 0,DealerKey_Org, DealerKey_NewFix) as DealerKey_FINAL

Resident SalesTrans2;

DROP Table SalesTrans2;

DROP Fields DealerKey_Org, DealerKey_NewFix;