Concatenation of Names using SQL to Update Maps

Question

question

Abhay Bajpai asked Jul 27, '23 Jason Lightfoot commented Sep 18, '23

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

5 |100000

Attachments: Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

Jason Lightfoot ♦♦ commented · Jul 27, 2023 at 05: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 07: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 ·

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

Carter Walch answered Jul 27, '23 Abhay Bajpai edited Sep 18, '23

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

5 |100000

Attachments: Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

Abhay Bajpai commented · Jul 28, 2023 at 03: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 04: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 05: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 08: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 09: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 07: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 02: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