Concatenation of Names using SQL to Update Maps

Question

question

Abhay Bajpai asked Jul 27 2023 at 3:39 PM Jason Lightfoot commented Sep 18 2023 at 2:37 PM

Concatenation of Names using SQL to Update Maps

Objective: To put unique names (in a map called "PartMap") of item that need to be summoned at each station. Each station has specific items that get added on top of the flow item "Floor".

I have 8 columns. Column 1 is stationID. Column 2 is MODEL_NAME. Column 3 MODE_PART and Column 4 onwards to column 8 are for options. Options header should be concatenated to the end of Model_Part if the the value in the row for that Model_Part under an option is "1". If it is blank, then no concatenation required. The nomenclature I am trying to achieve should be "MODEL_NANE-MODEL_PART-OPTION header" (OPTION is conditional if present)

Model and Part name columns have values in all rows but not the option names. Option names have only have one value "1". Columns 3 through 10 headers are the names of the options. If that option has a value 1 against the model name, that means that option is present in the model. For example, row 1 has a model name "Model 1", part name "Floor" and under column 3 option header "Option 1", there is the number 1 means that Option 1 is present in Model 1's Part "Floor". Another example, | Model 2 | Wall | 0 | then it should display "Model 2-Wall" since The Model_Part "WALL" does not have any options.

@Jason Lightfoot

ChampionHomes_V7_Abhay_1.fsm

Software Version:

FlexSim 23.1.2

sql queries

1690471151403.png (185.1 KiB)

1690471248484.png (25.5 KiB)

championhomes-v7-abhay-1.fsm (2.4 MiB)

· 2

Jason Lightfoot ♦♦ commented · Jul 27 2023 at 5:46 PM

Can you describe what your trying to do at a higher level to see if there's a better way that this concatenation?

0 ·

Abhay Bajpai Jason Lightfoot ♦♦ commented · Jul 27 2023 at 7:38 PM

So the parts that go on top of the flow item now come with various options that customers can add. So i thought i would create a repository of options similar to parts in the flor bin. For example a standard floor part can be called "M1A_Floor_A" which is a floor on the A side of house of model M1. If customer chooses an option 1 or option 2 in Floor_A, then flexsim will need to search 'Option 1' of 'Floor_A'. So i thought i would create an item called "M1A_Floor_A_Option 1". Do you think there is a better way?

0 ·

______

Cookie preferences

Your privacy is important to us and so is an optimal experience. To help us customize information and build applications, we collect data about your use of this site.

May we collect and use your data?

Learn more about the Third Party Services we use and our Privacy Statement.

Strictly necessary – required for our site to work and to provide services to you

These cookies allow us to record your preferences or login information, respond to your requests or fulfill items in your shopping cart.

YES

Improve your experience – allows us to show you what is relevant to you

These cookies enable us to provide enhanced functionality and personalization. They may be set by us or by third party providers whose services we use to deliver information and experiences tailored to you. If you do not allow these cookies, some or all of these services may not be available for you.

YES

NO

Customize your advertising – permits us to offer targeted advertising to you

These cookies collect data about you based on your activities and interests in order to show you relevant ads and to track effectiveness. By collecting this data, the ads you see will be more tailored to your interests. If you do not allow these cookies, you will experience less targeted advertising.

YES

NO

Are you sure you want a less customized experience?

We can access your data only if you select "yes" for the categories on the previous screen. This lets us tailor our marketing so that it's more relevant for you. You can change your settings at any time by visiting our privacy statement

Your experience. Your choice.

We care about your privacy. The data we collect helps us understand how you use our products, what information you might be interested in, and what we can improve to make your engagement with Autodesk more rewarding.

May we collect and use your data to tailor your experience?

Explore the benefits of a customized experience by managing your privacy settings for this site or visit our Privacy Statement to learn more about your options.

Answer 1 · 2023-07-27T17:12:09Z

Carter Walch answered Jul 27 2023 at 5:12 PM Abhay Bajpai edited Sep 18 2023 at 10:40 PM

Hi @Abhay Bajpai ,

Here is a SQL query you can use to create your table with the concatenated column:

string sql = "SELECT MODEL_NAME, MODEL_PART, MODEL_NAME + '-' + MODEL_PART + '-' + \
CASE \
WHEN OPTION_1 = 1 THEN 'OPTION_1' \
WHEN OPTION_2 = 1 THEN 'OPTION_2' \
WHEN OPTION_3 = 1 THEN 'OPTION_3' \
WHEN OPTION_4 = 1 THEN 'OPTION_4' \
WHEN OPTION_5 = 1 THEN 'OPTION_5' \
ELSE '' \
END AS CONCATENATED_NAME \
FROM OptionsTable";
Table result=Table.query(sql);
result.cloneTo(Table("temp3"));

What do you want the value to look like when multiple Option columns have a 1 value? I also changed your column headers from 'OPTION 1' to OPTION_1 to avoid errors with spaces in names in SQL

1690478015534.png (17.6 KiB)

· 19

Abhay Bajpai commented · Jul 28 2023 at 3:21 PM

Good question. I believe if one column is checked, the other column won't be checked. There can only be one kind of option. But now that you brought it up, if multiple options were to be added, how would we update the code?

Also is there a way to execute the custom code without calling it in process flow? Just to see if the code works or for debugging purposes?

0 ·

Carter Walch Abhay Bajpai commented · Jul 28 2023 at 4:51 PM

Gotcha. If there can only be one option then the code above should work. You can modify it to look like this so the '-' doesn't show up if no option is selected:

string sql = "SELECT MODEL_NAME, MODEL_PART, MODEL_NAME + '-' + MODEL_PART + \
CASE \
WHEN OPTION_1 = 1 THEN '-OPTION_1' \
WHEN OPTION_2 = 1 THEN '-OPTION_2' \
WHEN OPTION_3 = 1 THEN '-OPTION_3' \
WHEN OPTION_4 = 1 THEN '-OPTION_4' \
WHEN OPTION_5 = 1 THEN '-OPTION_5' \
ELSE '' \
END AS CONCATENATED_NAME \
FROM OptionsTable";
Table result=Table.query(sql);
result.cloneTo(Table("temp4"));

If multiple options are to be added, you can modify the code to have several CASE statements and concatenate the string only if there is a 1 in that column. Here is what that looks like:

string sql = "SELECT MODEL_NAME, MODEL_PART, MODEL_NAME + '-' + MODEL_PART + \
CASE WHEN OPTION_1 = 1 THEN '-OPTION_1' ELSE '' END + \
CASE WHEN OPTION_2 = 1 THEN '-OPTION_2' ELSE '' END + \
CASE WHEN OPTION_3 = 1 THEN '-OPTION_3' ELSE '' END + \
CASE WHEN OPTION_4 = 1 THEN '-OPTION_4' ELSE '' END + \
CASE WHEN OPTION_5 = 1 THEN '-OPTION_5' ELSE '' END AS CONCATENATED_NAME \
FROM OptionsTable";
Table result=Table.query(sql);
result.cloneTo(Table("temp3"));

For example, Row 8 has all options checked so they are all added to the concatenated name:

A useful tool for executing code and debugging is the Script Console (see link for documentation). If you paste either of the code blocks above into a script window and execute them, the changes will be made to your tables. Here is a gif demonstrating that:

1 ·

1690562696853.png (7.7 KiB)

1690562874491.png (1.9 KiB)

1690562902251.png (21.3 KiB)

scriptwindow.gif (476.3 KiB)

Abhay Bajpai Carter Walch commented · Jul 28 2023 at 5:47 PM

Wonderful! That was very helpful!

Use case: the use of the concatenated name is to then use a Map function at each station to call on the concatenated names for the respective stations. For station 1, the map should store the concatenated values corresponding to the stations. Do you think this is the correct approach?

Also, I have been trying to find the "Accept Answer" button under my post but cannot seem to find it.

0 ·

1690566369409.png (25.4 KiB)

Show more comments

Abhay Bajpai commented · Jul 31 2023 at 8:36 PM

@Carter Walch i tried using the SQL for a similar issue where the logic is: TAKE CYCLE TIME AS THE BASELINE AND ADD OPTION VALUES TO CYCLE TIME IF THE OPTION VALUE IS MORE THAN 1 and name that column as Aggregated_Time. For example, if cycle time is 100 and option has any value other than 0,say 50, it should take that options value (50) and add it to cycle time (100) and make it 150 under Aggregated_time for that Model_Name. I tried it but for some reason i cannot seem to parse the string through the query.ChampionHomes_V7_Abhay_2.fsm

0 ·

1690836818127.png (68.2 KiB)

championhomes-v7-abhay-2.fsm (2.4 MiB)

Carter Walch Abhay Bajpai commented · Jul 31 2023 at 9:47 PM

Hi @Abhay Bajpai ,

Can you re-attach your model? I can't download it

0 ·

Abhay Bajpai Carter Walch commented · Aug 01 2023 at 12:09 AM

ChampionHomes_V7_Abhay_2.fsm Hi Carter, try this one. Let me know if it worked. I also reattached the model in my previous comment just in case

1 ·

Show more comments

Abhay Bajpai commented · Sep 15 2023 at 7:51 PM

Hey Carter,

Here is a weird error that i received upon clicking on "Apply All" in the User Command "WriteStationMaps".

Basically after making changes, I clicked on "Apply" button instead and then ran the model and it would not parse the string saved in 'sql' variable. It will just skip the iteration.

I was wondering if you could help me with this error. I have attached the model here.

ChampionHomes_V7_Abhay_7_autosave.fsm

0 ·

1694807672942.png (155.1 KiB)

championhomes-v7-abhay-7-autosave.fsm (16.4 MiB)

Jason Lightfoot ♦♦ Abhay Bajpai commented · Sep 15 2023 at 11:01 PM

Then don't hit apply all and just apply the code that you want to apply. The problem is with the NULL tab - it's not linked to a valid code node - so like the error message says - if you want to apply that code somewhere, copy the code and place it where you want before closing that window.

1 ·

Abhay Bajpai Jason Lightfoot ♦♦ commented · Sep 18 2023 at 2:29 PM

Got it! If i "Apply all" it applied all the custom codes instead of just the one custom code that I am on, correct?

0 ·

Show more comments

question