topic Re: Concatenate Tables With Field Using Dual and Match Functions in App Development

Concatenate Tables With Field Using Dual and Match Functions

shannonmarshall329 — Mon, 05 Aug 2024 14:29:31 GMT

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

Re: Concatenate Tables With Field Using Dual and Match Functions

rwunderlich — Mon, 05 Aug 2024 18:29:33 GMT

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

-Rob

Re: Concatenate Tables With Field Using Dual and Match Functions

shannonmarshall329 — Mon, 05 Aug 2024 18:44:01 GMT

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]

Re: Concatenate Tables With Field Using Dual and Match Functions

shannonmarshall329 — Mon, 05 Aug 2024 18:49:32 GMT

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?

Re: Concatenate Tables With Field Using Dual and Match Functions

rwunderlich — Tue, 06 Aug 2024 03:57:35 GMT

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

Re: Concatenate Tables With Field Using Dual and Match Functions

shannonmarshall329 — Mon, 12 Aug 2024 11:34:52 GMT

Rob, thanks for the suggestion