[Solved] Quantity/size grid, vertical to horizontal and join

[cartoonjazz]

I appreciate any help that can be offered.
I have a table that shows skus including size, price and quantity listed vertically. I would like to generate a horizontal grid grouping all items whose sku are the same before the first dash from the left.

There is some inconsistency in the naming convention of the sizes. Size is always at the end of the sku so the query should look before the first dash from the right for an "X". If there is no number before the "X" look for a number or "X" before the first dash from the right of the "X". This should take a skus like "2-X-Large", "2X-Large", "2XLarge" or "XX-Large" and list the quantity in the "2XL" column. The same should apply to XS, XL, 3XL, 4XL and 5XL.

The Price column should be an average of all prices for that grouped sku

table 1

raw.jpg (17.83 KiB) Viewed 5748 times

result

result.jpg (14.18 KiB) Viewed 5748 times

thanks again

[Sliderule]

I created a database ( SKU.odb ) for you, based ( pun intended ) on the example as defined above, so you can explore:

1. 1 Table - name of "TABLE1"
2. 4 Views
3. 4 Queries

Since your SKU content is so convoluted, it will be up to you to further explore how to handle it.

SKU.odb

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[cartoonjazz]

Brilliant! works great I imagine that what need to do is edit the "TABLE1_SHORT_SIZE_QUERY" query and view and add all the possible variations of the sizes for example
SELECT CASE WHEN "TABLE1_SKU_SIZE_QUERY"."SIZE" = 'X-Small' THEN 'XS' WHEN "TABLE1_SKU_SIZE_QUERY"."SIZE" = 'XSmall' THEN 'XS'
wash rinse repeat . when i try to modify the view it won't let me save changes with error
"Table is referenced by view: TABLE1_STYLE_CROSSTAB_VIEW in statement [DROP VIEW "TABLE1_SHORT_SIZE_VIEW"]"

I get a similar error when changing the query but it does save the change will this cause a problem?

[Sliderule]

I do not understand what you are asking.

The VIEWs were created from the Querys. That is, while the VIEWs may not be needed, if you do not want . . . that is OK. I could only answer the question you asked, based on your example AND the output you said you wanted.

If you want to DROP the VIEWs . . . do it in this order:

1. TABLE1_SKU_VIEW
2. TABLE1_SKU_SIZE_VIEW
3. TABLE1_SHORT_SIZE_VIEW
4. TABLE1_STYLE_CROSSTAB_VIEW

You could then, CREATE a VIEW based on your modified QUERY with whatever name you prefer.

Please change your Querys as you require. It is up to you do do this.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[cartoonjazz]

Yes of course thanks for the info
Please confirm the syntax to add additional forms of the Extra Small size for instance would be the following

SELECT CASE WHEN "TABLE1_SKU_SIZE_QUERY"."SIZE" = 'X-Small' THEN 'XS' WHEN "TABLE1_SKU_SIZE_QUERY"."SIZE" = 'XSmall' THEN 'XS'

[Sliderule]

You asked / wrote it as:

Code: Select all

SELECT CASE WHEN "TABLE1_SKU_SIZE_QUERY"."SIZE" = 'X-Small' THEN 'XS' WHEN "TABLE1_SKU_SIZE_QUERY"."SIZE" = 'XSmall' THEN 'XS'

I would suggest the following:

Code: Select all

SELECT CASE WHEN "TABLE1_SKU_SIZE_QUERY"."SIZE" IN ('X-Small', 'XSmall') THEN 'XS' 

However, you could write it either way. The advantage for doing the way I am suggesting is, using IN ( rather than an = ), with a list of possibilities surrounded by parentheses, you could include a multitude of possibilities withOUT having to repeat the WHEN and condition.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[cartoonjazz]

exactly what I was looking for that's great thanks again

[cartoonjazz]

Any help gratefully accepted. This has been working great for most skus however some items are not displaying in the report. I believe it is the result of the query used to seperate the size from the sku. I checked TABLE1_SKU_QUERY for sku 724200 and the size column shows the last 2 digits of the sku with the size section & 4765 leaves the dash seperator in before the size.
i.e. "725400-Small" shows in the size column as "00-Small"
also "4765-Medium" shows in the size column as "-Medium"

As a result they are not displayed in the final report query "TABLE1_STYLE_CROSSTAB_QUERY"

Other skus with alpha characters after the the style# and before the size do show correctly
i.e. "725099-Size-X-Large" shows in the size column as "X-Large" this does show in the final query

download database at https://ufile.io/3t5xl it's too big, 450kb, to upload as attachment here

[Sliderule]

I do not wish to download your database, since, I suspect it is up to you to determine the solution.

Each calculated output you showed seems to have a length of 7 characters . . . ¿ do you see a pattern ?

As I stated earlier in a prior post

Since your SKU content is so convoluted, it will be up to you to further explore how to handle it.

Sliderule

[cartoonjazz]

I don't mind doing the work but I think my SQL level is not up to this level, any help pointing me in the right direction is appreciated.
by "calculated output" do you mean "-Medium" & "00-Small" ?

[Sliderule]

I don't mind doing the work but I think my SQL level is not up to this level, any help pointing me in the right direction is appreciated.
by "calculated output" do you mean "-Medium" & "00-Small" ?

Since, in the database, a text string is surrounded by single quotes, not double quotes, and, double quotes indicates a table, view, or column name, I mean, it is up to you to determine what is incorrect in your data, and / or, calculations.

Sliderule

[cartoonjazz]

Thanks for the push in the right dfirection I believe it must be the following calculation
"SUBSTR( "SKU", LOCATE( 'Size', "SKU" ) + 4 + 1 )"
not sure how this function works . I expect its extracting a substring of the sku field based on position. I expect i would like to leave it in because it's workjing for some skus but put a condition that it be applied for skus that have the words "color" or "size" in them . If not I would like it to extract the size from everything after the dash.

[Sliderule]

Code: Select all

"SUBSTR( "SKU", LOCATE( 'Size', "SKU" ) + 4 + 1 )"

Try the following code:

Code: Select all

SUBSTR( "SKU", CASE WHEN LOCATE('Size', "SKU") = 0 THEN LOCATE('-', "SKU") + 1 ELSE LOCATE( 'Size', "SKU" ) + 4 + 1 END )

To see documentation of Built-in Functions available when using HSQL Version 1.8.0.10 ( the Embedded Database ) see the following:

http://www.hsqldb.org/doc/1.8/guide/ch09.html#N1251E

Sliderule

[cartoonjazz]

will look into that, works great thanks much

[Sliderule]

will look into that, works great thanks much

Since, your original question is resolved, as asked in my first 3 posts to you here, please ( and thank-you very much ) add:

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.