topic Re: Concatenation of Names using SQL to Update Maps in FlexSim Forum

Concatenation of Names using SQL to Update Maps

ABajpaiWMKNX — Thu, 27 Jul 2023 15:39:06 GMT

[ FlexSim 23.1.2 ]

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

Re: Concatenation of Names using SQL to Update Maps

carter-walch — Thu, 27 Jul 2023 17:12:09 GMT

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

Re: Concatenation of Names using SQL to Update Maps

jason_lightfoot_adsk — Thu, 27 Jul 2023 17:46:50 GMT

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

Re: Concatenation of Names using SQL to Update Maps

ABajpaiWMKNX — Thu, 27 Jul 2023 19:38:42 GMT

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?

Re: Concatenation of Names using SQL to Update Maps

ABajpaiWMKNX — Fri, 28 Jul 2023 15:21:38 GMT

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?

Re: Concatenation of Names using SQL to Update Maps

carter-walch — Fri, 28 Jul 2023 16:51:32 GMT

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:

Re: Concatenation of Names using SQL to Update Maps

ABajpaiWMKNX — Fri, 28 Jul 2023 17:47:20 GMT

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.

Re: Concatenation of Names using SQL to Update Maps

carter-walch — Fri, 28 Jul 2023 18:06:17 GMT

Glad it helped! Yes I believe that using a Map function would work to accomplish this as long as the keys match what the customers are choosing

And I now converted it to an answer instead of a comment

Re: Concatenation of Names using SQL to Update Maps

ABajpaiWMKNX — Mon, 31 Jul 2023 20:36:02 GMT

@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

Re: Concatenation of Names using SQL to Update Maps

carter-walch — Mon, 31 Jul 2023 21:47:54 GMT

Hi @Abhay Bajpai ,

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

Re: Concatenation of Names using SQL to Update Maps

ABajpaiWMKNX — Tue, 01 Aug 2023 00:09:54 GMT

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

Re: Concatenation of Names using SQL to Update Maps

carter-walch — Tue, 01 Aug 2023 16:00:30 GMT

Thank you, reattaching it worked. I may have misunderstood but aren't the Option values only 0 or 1? Or are you wanting to pull them from another table?

This query makes the AggregatedTime column by adding the option value to CYCLE_TIME

sql="SELECT CYCLE_TIME, OPTION_1, OPTION_2, OPTION_3, OPTION_4, OPTION_5,  \
CASE \
WHEN OPTION_1 > 0 THEN CYCLE_TIME + OPTION_1 \
WHEN OPTION_2 > 0 THEN CYCLE_TIME + OPTION_2 \
WHEN OPTION_3 > 0 THEN CYCLE_TIME + OPTION_3 \
WHEN OPTION_4 > 0 THEN CYCLE_TIME + OPTION_4 \
WHEN OPTION_5 > 0 THEN CYCLE_TIME + OPTION_5 \
ELSE CYCLE_TIME \
END AS AggregatedTime \
FROM OptionsTable \
WHERE stationID='"+stationID+"'";

Re: Concatenation of Names using SQL to Update Maps

ABajpaiWMKNX — Tue, 01 Aug 2023 16:07:02 GMT

Carter, you understood it perfectly. The Options columns had 1 or 0 initially as the binary input of saying if the option is present or not.

I think I messed up the vision on how I should be achieving this. I think i will have to make another global table with 'Model Name' and 'Options_1 time','Options_2 time','Options_3 time' etc to add their times to 'Cycle Time' if their respective 'Options_1' column in table OptionsTable have a value "1" in it. If they are not present (having the 0 value in OptionsTable), then the time for that option will not be added to Cycle Time. Hope this makes sense. I apologize for being non-descript earlier.

I guess I will somehow have to merge both of the solutions you provided.

Re: Concatenation of Names using SQL to Update Maps

carter-walch — Tue, 01 Aug 2023 16:17:22 GMT

No that makes perfect sense. If you make that table with each Options time value, we can easily join that table in the SQL query and pull the actual time values to add to CYCLE_TIME. Let me know if you need anything in that process

Re: Concatenation of Names using SQL to Update Maps

ABajpaiWMKNX — Thu, 03 Aug 2023 14:29:14 GMT

Ok so here is what i think: There are two tables of interest here: OptionsTable and OptionsTimeTable.

OptionsTable:

OptionsTimeTable:

I guess the query should say: Merge two tables where the value under each Options column is 1 in OptionsTable and display the number from OptionsTimeTable? Am I thinking right? If you can help me with the statement, that would be awesome!

ChampionHomes_V7_Abhay_2.fsm

Re: Concatenation of Names using SQL to Update Maps

carter-walch — Thu, 03 Aug 2023 16:50:19 GMT

Yes that is correct. As long as we have a primary key that connects the two tables (in this case a composite primary key of stationID and MODEL_NAME), we can join the tables so that when an Option is 1 in the OptionsTable, we will add the time value from OptionsTimeTable

I believe the model you attached is an older version because I don't see the OptionsTimeTable, I have a query that should work but if you reattach the new version I can test it

sql="SELECT CYCLE_TIME, OPTION_1, OPTION_2, OPTION_3, OPTION_4, OPTION_5,  \
CASE \
WHEN OPTION_1 > 0 THEN CYCLE_TIME + OptionsTimeTable.[OPTION 1] \
WHEN OPTION_2 > 0 THEN CYCLE_TIME + OptionsTimeTable.[OPTION 2] \
WHEN OPTION_3 > 0 THEN CYCLE_TIME + OptionsTimeTable.[OPTION 3] \
WHEN OPTION_4 > 0 THEN CYCLE_TIME + OptionsTimeTable.[OPTION 4] \
WHEN OPTION_5 > 0 THEN CYCLE_TIME + OptionsTimeTable.[OPTION 5] \
ELSE CYCLE_TIME \
END AS AggregatedTime \
FROM OptionsTable, OptionsTimeTable \
WHERE \
stationID='"+stationID+"'\
AND OptionsTable.stationID = OptionsTimeTable.STATION \
AND OptionsTable.MODEL_NAME = OptionsTimeTable.[MODEL NAME]";

Re: Concatenation of Names using SQL to Update Maps

ABajpaiWMKNX — Thu, 03 Aug 2023 17:44:21 GMT

Apologize for that. I have updated the model in the previous comment and attached it here as well.

ChampionHomes_V7_Abhay_2.fsm

Re: Concatenation of Names using SQL to Update Maps

ABajpaiWMKNX — Thu, 03 Aug 2023 17:58:32 GMT

Carter, it worked like a charm! Thanks a lot buddy! Is SQL on Flexsim's website the only source to learn about it?

Re: Concatenation of Names using SQL to Update Maps

carter-walch — Thu, 03 Aug 2023 18:52:37 GMT

Sure thing! I'm glad it worked out! There's a lot of online material for learning SQL like w3schools, but the FlexSim manual is very useful for what SQL commands specifically work in FlexSim.

For example, I like this site for learning INNER JOIN but I double check the FlexSim syntax in the manual (see 'Joins') to see how you would do the same thing in FlexSim.

Hope that helps!

Re: Concatenation of Names using SQL to Update Maps

ABajpaiWMKNX — Fri, 15 Sep 2023 19:51:49 GMT

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