Jump to content

Creating a master item list


Recommended Posts

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 by riceking

\

Link to comment
Share on other sites

Link to comment
Share on other sites

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]

\

Link to comment
Share on other sites

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 by pixelsearch
Link to comment
Share on other sites

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

\

Link to comment
Share on other sites

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 by riceking

\

Link to comment
Share on other sites

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

Code hard, but don’t hard code...

Link to comment
Share on other sites

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!

\

Link to comment
Share on other sites

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 by riceking

\

Link to comment
Share on other sites

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.

Code hard, but don’t hard code...

Link to comment
Share on other sites

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!

\

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...