Concatenate Tables With Field Using Dual and Match Functions

[shannonmarshall329]

Hello, I am trying to concatenate two tables together and one of the fields uses a Dual/Match function to set the order of the data during the load.  When I load the data the field in the first table shows correctly, however, the data in the second table gives me the numeric value instead of the character value.  Does anyone know what might be causing this and if there is a way to get the character values to show for both loads?

Thanks

[rwunderlich]

Are you applying the function in both load statements? Can you post the load statement?

-Rob

[shannonmarshall329]

Rob, here is the load statement for both fields that are in a transform qvd.  Just noticing that the statements are not identical.  Do they need to be or can I just update so the order of the bigger one matches the order of the smaller one and add the extra values at the end?

DUAL(COMBINED_BUSINESS_UNIT_TEMP,
Match(COMBINED_BUSINESS_UNIT_TEMP,'PRS','HRMO','PRM','DAC','PCO','CRD','UXD','OPS','ASD','ITS','CNE','MAT','MWT','STH',
'WST','DSMC','AWQ','CCM','DAU','EID','FLD','HCI','PLD','WLD','UNKNOWN')) AS COMBINED_BUSINESS_UNIT,

DUAL([Emp_Region_Code_Temp], MATCH([Emp_Region_Code_Temp],'PRS','HRMO','PPL','PRM','CLO','DAC','PCO','CRD','UXD','MKR','PLD','GLT','LTC','FLD','LCI','WLD','EID',
'OPS','CDS','ASD','ITS','CNE','MAT','MWT','STH','WST','DSC','DSMC','LKR','CCM','HCI','AWC','UNK','AWQ','TES','Centrally Managed',
'DACM Student Travel','CIVP', 'RES','LIB','LPT','000','APS','DAU','UNKNOWN')) as [Emp Region Code]

[shannonmarshall329]

Another note.  The field I am using that represents the smaller set (COMBINED_BUSINESS_UNIT) is actually not pulling from the field where the function is being done.  It is another field in a different table that has the Dual(Match) function applied.  Do they have to happen in the same load like autonumber?

[rwunderlich · Accepted solution]

If your intent is to assign a consistent numeric value for sorting, then I would say it is "wrong" to have the two lists having different number of elements and/or in a different order. 

If your intent is to establish a sort order with this many values, you may find loading a temp table to be easier https://qlikviewcookbook.com/2012/11/establishing-a-sort-order/

For Qlik Sense, "load order" in a chart means to uncheck all the sorting check boxes. In those circumstances where you must use a sort expression, you can use the function 

FieldIndex('fieldname', fieldname)

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[shannonmarshall329]

Rob, thanks for the suggestion