Automate

I am attempting to create a query using Customer Sold-To, Ship-To data and associated freight rates.  The output will become a "Look Up" sheet in an Excel workbook.  The purpose of this sheet is to function as the source of a VLookup from a separate sheet in the workbook.  So, it is important that the output be in a specific order.  That required order is Sales Organization, Division, Customer Sold-To, Customer.  It's also important that the user is not required to manipulate or order this Look Up sheet once it's created.

Since Query is limited to a single column for sorting, I've attempted to create a single Sort Key column as a concatenation of the four fields or columns shown above.  The Transformation Rule for this is

               =CONCATENATE(B2, D2, A2, F2) and results are stored in column AA2

When the resulting spreadsheet is viewed the Sort Key value in column AA2 appears correct.  However, the column cannot be sorted.  Clicking on a cell in this column shows the formula contents (or instructions), rather than the actual resulting value of the formula.

I have not been able to find a way to capture the actual resulting value of the formula.  I've attempted to use Excel functions "=VALUE" and "TEXT" to no avail.

I've included a copy of the query.  Appreciate any suggestions you can offer for how this can be accomplished.

------------------------------
Dave Leon
KIS Technologies
------------------------------

Hi

For testing I created a query with material number, plant and follow up material and added a =concatenate( -formula using transform rule in the query. I published and ran the query and then was able to Sort in the spreadsheet using excel from the concatenated column. I'm not sure why it wouldn't work with you.

If the query result is going to be used as a vlookup table, why should the results be sorted? And why would the vlookup key be in the end of the table (column AA)? Could you provide screenshots of how the excel file looks and how it's built?

------------------------------
--
------------------------------

Good morning,
Another possibility could be to have a little VBA-script running on the spreadsheet. Such a script could fill a column with the concatenate you mention and have the results as values in stead of as a formula.

------------------------------
jan ketele | Sr engineer release mgt
Xeikon Manufacturing NV | +3234431875
------------------------------

Original Message:
Sent: 07-26-2022 03:26
From: Henri Mäntysaari
Subject: Resulting Value for Concatenation

Hi

For testing I created a query with material number, plant and follow up material and added a =concatenate( -formula using transform rule in the query. I published and ran the query and then was able to Sort in the spreadsheet using excel from the concatenated column. I'm not sure why it wouldn't work with you.

If the query result is going to be used as a vlookup table, why should the results be sorted? And why would the vlookup key be in the end of the table (column AA)? Could you provide screenshots of how the excel file looks and how it's built?

------------------------------
--
------------------------------

Henri - could you please provide a copy of you query where you were able to sort the concatenated field values?

------------------------------
Dave Leon
KIS Technologies
------------------------------

Original Message:
Sent: 07-26-2022 03:26
From: Henri Mäntysaari
Subject: Resulting Value for Concatenation

Hi

For testing I created a query with material number, plant and follow up material and added a =concatenate( -formula using transform rule in the query. I published and ran the query and then was able to Sort in the spreadsheet using excel from the concatenated column. I'm not sure why it wouldn't work with you.

If the query result is going to be used as a vlookup table, why should the results be sorted? And why would the vlookup key be in the end of the table (column AA)? Could you provide screenshots of how the excel file looks and how it's built?

------------------------------
--
------------------------------

Sorry, I'm not able to share the file itself but here are relevant screenshots

Workspace

Criteria

Mapping

Transform rule

After publishing to excel, running and adding filters to the top row it's possible to sort

------------------------------
--
------------------------------

Original Message:
Sent: 07-27-2022 10:37
From: Dave Leon
Subject: Resulting Value for Concatenation

Henri - could you please provide a copy of you query where you were able to sort the concatenated field values?

------------------------------
Dave Leon
KIS Technologies
------------------------------


Original Message:
Sent: 07-26-2022 03:26
From: Henri Mäntysaari
Subject: Resulting Value for Concatenation

Hi

For testing I created a query with material number, plant and follow up material and added a =concatenate( -formula using transform rule in the query. I published and ran the query and then was able to Sort in the spreadsheet using excel from the concatenated column. I'm not sure why it wouldn't work with you.

If the query result is going to be used as a vlookup table, why should the results be sorted? And why would the vlookup key be in the end of the table (column AA)? Could you provide screenshots of how the excel file looks and how it's built?

------------------------------
--

Henry - your transform =concatenate(b2;c2;d2) uses semicolons rather than comma's.   I get an error in the transformation when I use semicolons.

Have you checked the accuracy of the sorted data in your column E to insure it is being sorted correctly?

Thanks

------------------------------
Dave Leon
KIS Technologies
------------------------------

Original Message:
Sent: 07-28-2022 03:22
From: Henri Mäntysaari
Subject: Resulting Value for Concatenation

Sorry, I'm not able to share the file itself but here are relevant screenshots

Workspace

Criteria

Mapping

Transform rule

After publishing to excel, running and adding filters to the top row it's possible to sort

------------------------------
--
------------------------------


Original Message:
Sent: 07-27-2022 10:37
From: Dave Leon
Subject: Resulting Value for Concatenation

Henri - could you please provide a copy of you query where you were able to sort the concatenated field values?

------------------------------
Dave Leon
KIS Technologies


Original Message:
Sent: 07-26-2022 03:26
From: Henri Mäntysaari
Subject: Resulting Value for Concatenation

Hi

For testing I created a query with material number, plant and follow up material and added a =concatenate( -formula using transform rule in the query. I published and ran the query and then was able to Sort in the spreadsheet using excel from the concatenated column. I'm not sure why it wouldn't work with you.

If the query result is going to be used as a vlookup table, why should the results be sorted? And why would the vlookup key be in the end of the table (column AA)? Could you provide screenshots of how the excel file looks and how it's built?

------------------------------

Hi, sorry, I was on a holiday so I'm replying now.

The semicolons are due to my PC's Office localization. It uses ; instead of ,.

Hmm, actually I didn't originally separately check the correctness of the sorting, I assumed it would work in a normal excel manner. I did check now and they seem to be in correct A->Z order and when turning around correctly Z->A. This was a test however, the listing didn't include many different material numbers.

------------------------------
--
------------------------------

Original Message:
Sent: 07-30-2022 15:25
From: Dave Leon
Subject: Resulting Value for Concatenation

Henry - your transform =concatenate(b2;c2;d2) uses semicolons rather than comma's.   I get an error in the transformation when I use semicolons.

Have you checked the accuracy of the sorted data in your column E to insure it is being sorted correctly?

Thanks

------------------------------
Dave Leon
KIS Technologies
------------------------------


Original Message:
Sent: 07-28-2022 03:22
From: Henri Mäntysaari
Subject: Resulting Value for Concatenation

Sorry, I'm not able to share the file itself but here are relevant screenshots

Workspace

Criteria

Mapping

Transform rule

After publishing to excel, running and adding filters to the top row it's possible to sort

------------------------------
--


Original Message:
Sent: 07-27-2022 10:37
From: Dave Leon
Subject: Resulting Value for Concatenation

Henri - could you please provide a copy of you query where you were able to sort the concatenated field values?

------------------------------
Dave Leon
KIS Technologies


Original Message:
Sent: 07-26-2022 03:26
From: Henri Mäntysaari
Subject: Resulting Value for Concatenation

Hi

For testing I created a query with material number, plant and follow up material and added a =concatenate( -formula using transform rule in the query. I published and ran the query and then was able to Sort in the spreadsheet using excel from the concatenated column. I'm not sure why it wouldn't work with you.

If the query result is going to be used as a vlookup table, why should the results be sorted? And why would the vlookup key be in the end of the table (column AA)? Could you provide screenshots of how the excel file looks and how it's built?