Creating a master item list

riceking · April 27, 2021

Hello,

I was hoping to gather some ideas on the best way to generate a CSV of all item combinations to import as a database of some products. I was looking at some cartesian products implementation but there is unfortunately a snag I came across -

ProductLine 1 has the following features

Size1

Size2

Size5

Thickness1

Thickness2

Thickness3

Color1

Color2

Color3

ProductLine 2 has the following features

Size1

Size2

Thickness1

Thickness3

Color1

Color2

Color3

Color4

Color5

Color6

So a straight on cartesian product won't work as it generates too many superfluous information. Does anyone have any ideas on a good way to generate this list? For reference there are 5 product lines and the configurations go on to 10 optionals. I'm looking to create a master item list of all possible combinations that I currently can manufacture. I think worst case scenario is bite the bullet and do the full cartesian products then remove superfluous information but hopefully it doesn't come to that.

Best regards,

rk

Edited April 27, 2021 by riceking

Nine · April 27, 2021

Could you give the expected result based on the example you just provided. It will help us understanding what is the target you are aiming at.

riceking · April 27, 2021

10 minutes ago, Nine said:

Could you give the expected result based on the example you just provided. It will help us understanding what is the target you are aiming at.

Woops! Sure thing!

Line1,Size1,Thick1,Color1

Line1,Size1,Thick1,Color2

Line1,Size1,Thick1,Color3

Line1,Size1,Thick2,Color1

...

Line1,Size5,Thick3,Color3

Line2,Size1,Thick1,Color1

...

Line2,Size1,Thick1,Color6

Line2,Size1,Thick3,Color1

...

Line2,Size2,Thick3,Color6

---

I'm imagining the column options to be like below.

Global $aLine[2] = ["Line1","Line2"]

Global $aSize[5] = [1,2,3,4,5]

Global $aThick[3] = [1,2,3]

Global $aColor[6] = [1,2,3,4,5,6]

BigDaddyO · April 27, 2021

depending on what type of database you are using, it might be easier to create the output as .json instead of .csv as you basically need an array of arrays which .json is good at storing and there are some udf's available for dealing with them.

pixelsearch · April 27, 2021

Hi riceking
This seems to work :

#include <Array.au3>
#include <File.au3>

Local $aLine[2] = ["Line1","Line2"]
Local $aSize[5] = [1,2,3,4,5]
Local $aThick[3] = [1,2,3]
Local $aColor[6] = [1,2,3,4,5,6]

Local $iNbCombi = Ubound($aLine) * Ubound($aSize) * Ubound($aThick) * Ubound($aColor) ; 180
Local $aCSV[$iNbCombi], $iCounter = - 1

For $i = 0 To Ubound($aLine) - 1
    For $j = 0 To Ubound($aSize) - 1
        For $k = 0 To Ubound($aThick) - 1
            For $l = 0 To Ubound($aColor) - 1
                $iCounter += 1 ; starts at 0
                $aCSV[$iCounter] = $aLine[$i] & ",Size" & $aSize[$j] & ",Thick" & $aThick[$k] & ",Color" & $aColor[$l]
            Next
        Next
    Next
Next

_ArrayDisplay($aCSV, "$aCSV")

Local $sFile = @ScriptDir & "\CSV Example.csv"
_FileWriteFromArray($sFile, $aCSV) ; CSV will be comma delimited
If Not @error Then
    ShellExecute("notepad.exe", $sFile)
Else
    Msgbox(0, "Error", "@error = " & @error & " returned by _FileWriteFromArray")
EndIf

Edited April 27, 2021 by pixelsearch

riceking · April 27, 2021

36 minutes ago, BigDaddyO said:

depending on what type of database you are using, it might be easier to create the output as .json instead of .csv as you basically need an array of arrays which .json is good at storing and there are some udf's available for dealing with them.

JSON isn't an option for importing for me unfortunately, only CSV or XML for the software I'm using. I'm curious you mentioned arrays in arrays, I feel like my dataset is arrays in arrays but my goal is to have it all "multiplied" out so that it's just a standard 2D db/array

riceking · April 27, 2021

6 minutes ago, pixelsearch said:

Hi riceking
This seems to work :

#include <Array.au3>
#include <File.au3>

Local $aLine[2] = ["Line1","Line2"]
Local $aSize[5] = [1,2,3,4,5]
Local $aThick[3] = [1,2,3]
Local $aColor[6] = [1,2,3,4,5,6]

$iNbCombi = Ubound($aLine) * Ubound($aSize) * Ubound($aThick) * Ubound($aColor) ; 180 in this example
Local $aCSV[$iNbCombi], $iCounter = - 1

For $i = 0 To Ubound($aLine) - 1
    For $j = 0 To Ubound($aSize) - 1
        For $k = 0 To Ubound($aThick) - 1
            For $l = 0 To Ubound($aColor) - 1
                $iCounter += 1
                $aCSV[$iCounter] = $aLine[$i] & ",Size" & $aSize[$j] & ",Thick" & $aThick[$k] & ",Color" & $aColor[$l]
            Next
        Next
    Next
Next

_ArrayDisplay($aCSV, "$aCSV")

Local $sFile = @ScriptDir & "\CSV Example.csv"
_FileWriteFromArray($sFile, $aCSV)
If Not @error Then
    ShellExecute("notepad.exe", $sFile)
Else
    Msgbox(0, "Error", "@error = " & @error & " returned by _FileWriteFromArray")
EndIf

Oh much cleaner than my version - this is very similar to what I currently have, just full on cartesian products of all the arrays. My concerns lie in the fact that various lines of products have various access to the configuration. Whether it be sizing issues, or a tier locked feature

--

Edit: worse comes to worse, I'll just edit the arrays per item line and combine all the CSVs

Edited April 27, 2021 by riceking

JockoDundee · April 27, 2021

Try this -

#pragma compile(Console,True)

Local $aR=StringSplit(FileRead($CmdLine[1]),@CRLF&@CRLF,1)

ForNest()

Func ForNest($iLevel=1, $sLine="")

Local $aOpts=StringSplit($ar[$iLevel], @CRLF,3)

For $sOpt In $aOpts
   If $iLevel<$ar[0] Then
      ForNest($iLevel+1, $sOpt &","& $sLine)
   Else
      ConsoleWrite($sLine & $sOpt &@CRLF)
   EndIf
Next

EndFunc

Input File (Per Product Line - Must Have Empty Line after each feature)

Size1
Size2
Size5

Thickness1
Thickness2
Thickness3

Color1
Color2
Color3

Output (to Console):

Thickness1,Size1,Color1
Thickness1,Size1,Color2
Thickness1,Size1,Color3
Thickness2,Size1,Color1
Thickness2,Size1,Color2
Thickness2,Size1,Color3
Thickness3,Size1,Color1
Thickness3,Size1,Color2
Thickness3,Size1,Color3
Thickness1,Size2,Color1
Thickness1,Size2,Color2
Thickness1,Size2,Color3
Thickness2,Size2,Color1
Thickness2,Size2,Color2
Thickness2,Size2,Color3
Thickness3,Size2,Color1
Thickness3,Size2,Color2
Thickness3,Size2,Color3
Thickness1,Size5,Color1
Thickness1,Size5,Color2
Thickness1,Size5,Color3
Thickness2,Size5,Color1
Thickness2,Size5,Color2
Thickness2,Size5,Color3
Thickness3,Size5,Color1
Thickness3,Size5,Color2
Thickness3,Size5,Color3

Usage: pl.exe <product line input file>

File Attached:

pl.au3 pl.txt

riceking · April 28, 2021

19 hours ago, JockoDundee said:

Try this -

#pragma compile(Console,True)

Local $aR=StringSplit(FileRead($CmdLine[1]),@CRLF&@CRLF,1)

ForNest()

Func ForNest($iLevel=1, $sLine="")

Local $aOpts=StringSplit($ar[$iLevel], @CRLF,3)

For $sOpt In $aOpts
   If $iLevel<$ar[0] Then
      ForNest($iLevel+1, $sOpt &","& $sLine)
   Else
      ConsoleWrite($sLine & $sOpt &@CRLF)
   EndIf
Next

EndFunc

Input File (Per Product Line - Must Have Empty Line after each feature)

Size1
Size2
Size5

Thickness1
Thickness2
Thickness3

Color1
Color2
Color3

Output (to Console):

Thickness1,Size1,Color1
Thickness1,Size1,Color2
Thickness1,Size1,Color3
Thickness2,Size1,Color1
Thickness2,Size1,Color2
Thickness2,Size1,Color3
Thickness3,Size1,Color1
Thickness3,Size1,Color2
Thickness3,Size1,Color3
Thickness1,Size2,Color1
Thickness1,Size2,Color2
Thickness1,Size2,Color3
Thickness2,Size2,Color1
Thickness2,Size2,Color2
Thickness2,Size2,Color3
Thickness3,Size2,Color1
Thickness3,Size2,Color2
Thickness3,Size2,Color3
Thickness1,Size5,Color1
Thickness1,Size5,Color2
Thickness1,Size5,Color3
Thickness2,Size5,Color1
Thickness2,Size5,Color2
Thickness2,Size5,Color3
Thickness3,Size5,Color1
Thickness3,Size5,Color2
Thickness3,Size5,Color3

Usage: pl.exe <product line input file>

File Attached:

pl.au3 352 B · 2 downloads pl.txt 83 B · 1 download

This'll be much faster, thanks!

riceking · April 28, 2021

37 minutes ago, riceking said:

This'll be much faster, thanks!

This is indeed much faster, unfortunately my columns are all jumbled unfortunately. A lot of my columns are Yes/No unfortunately. I'll look through your snippet to see if I can figure out a way to keep the order it is entered but recursion makes my brain hurt

edit:

after close inspection, it looks like the columns aren't consistent in their dataset either.

image.png.8b5baf952a56f037ff4fad519e1e8979.png

Edited April 28, 2021 by riceking

JockoDundee · April 28, 2021

12 minutes ago, riceking said:

unfortunately my columns are all jumbled unfortunately.

Do you mean my output or your input?

Can u give an example, it’s probably easily fixed...

JockoDundee · April 28, 2021

1 hour ago, riceking said:

to see if I can figure out a way to keep the order it is entered but recursion makes my brain hurt

#pragma compile(Console,True)

Local $aR=StringSplit(FileRead($CmdLine[1]),@CRLF&@CRLF,1)

ForNest()

Func ForNest($iLevel=1, $sLine="")

Local $aOpts=StringSplit($ar[$iLevel], @CRLF,3)

For $sOpt In $aOpts
   If $iLevel<$ar[0] Then
      ForNest($iLevel+1, $sLine &  $sOpt &",")
   Else
      ConsoleWrite($sLine & $sOpt &@CRLF)
   EndIf
Next

EndFunc

This should keep the order, tested it with three optionals only though.

JockoDundee · April 28, 2021

1 hour ago, riceking said:

after close inspection, it looks like the columns aren't consistent in their dataset either.

Can you give me the input file you ran to create this?

riceking · April 28, 2021

34 minutes ago, JockoDundee said:

#pragma compile(Console,True)

Local $aR=StringSplit(FileRead($CmdLine[1]),@CRLF&@CRLF,1)

ForNest()

Func ForNest($iLevel=1, $sLine="")

Local $aOpts=StringSplit($ar[$iLevel], @CRLF,3)

For $sOpt In $aOpts
   If $iLevel<$ar[0] Then
      ForNest($iLevel+1, $sLine &  $sOpt &",")
   Else
      ConsoleWrite($sLine & $sOpt &@CRLF)
   EndIf
Next

EndFunc

This should keep the order, tested it with three optionals only though.

This one did the trick, after digging through my input file, I found an erroneous comma 🤦‍♂️. That must've been what was shifting the data to different columns. After fixing that comma and running this updated script, it prints out perfectly. Thank you!

JockoDundee · April 28, 2021

14 minutes ago, riceking said:

Thank you!

No problem.

*Full disclosure, I’m a paid influencer for the AARP - American Association of Recursive Programmers

Block all input without UAC	Save/Retrieve Images to/from Text	Monitor Management (VCP commands)
Tool to search in text (au3) files	Date Range Picker	Virtual Desktop Manager
Sudoku Game 2020	Overlapped Named Pipe IPC	HotString 2.0 - Hot keys with string
x64 Bitwise Operations	Multi-keyboards HotKeySet	Recursive Array Display
Fast and simple WCD IPC	Multiple Folders Selector	Printer Manager
GIF Animation (cached)	Screen Scraping	Multi-Threading Made Easy

Creating a master item list

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members