[Solved] Quantity/size grid, vertical to horizontal and join
-
- Posts: 54
- Joined: Sun Sep 04, 2016 9:40 pm
[Solved] Quantity/size grid, vertical to horizontal and join
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 result thanks again
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 result thanks again
Last edited by cartoonjazz on Fri Oct 06, 2017 5:09 pm, edited 3 times in total.
openoffice 4.1.2 windows 10
Re: quantity/size grid, vertical to horizontal and join
I created a database ( SKU.odb ) for you, based ( pun intended ) on the example as defined above, so you can explore:
Sliderule
Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
- 1 Table - name of "TABLE1"
- 4 Views
- 4 Queries
Sliderule
Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
-
- Posts: 54
- Joined: Sun Sep 04, 2016 9:40 pm
Re: quantity/size grid, vertical to horizontal and join
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?
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?
openoffice 4.1.2 windows 10
Re: Quantity/size grid, vertical to horizontal and join
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:
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.
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:
- TABLE1_SKU_VIEW
- TABLE1_SKU_SIZE_VIEW
- TABLE1_SHORT_SIZE_VIEW
- TABLE1_STYLE_CROSSTAB_VIEW
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.
-
- Posts: 54
- Joined: Sun Sep 04, 2016 9:40 pm
Re: Quantity/size grid, vertical to horizontal and join
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'
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'
openoffice 4.1.2 windows 10
Re: Quantity/size grid, vertical to horizontal and join
You asked / wrote it as:
I would suggest the following:
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.
Code: Select all
SELECT CASE WHEN "TABLE1_SKU_SIZE_QUERY"."SIZE" = 'X-Small' THEN 'XS' WHEN "TABLE1_SKU_SIZE_QUERY"."SIZE" = 'XSmall' THEN 'XS'
Code: Select all
SELECT CASE WHEN "TABLE1_SKU_SIZE_QUERY"."SIZE" IN ('X-Small', 'XSmall') THEN 'XS'
Sliderule
Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
-
- Posts: 54
- Joined: Sun Sep 04, 2016 9:40 pm
Re: Quantity/size grid, vertical to horizontal and join
exactly what I was looking for that's great thanks again
openoffice 4.1.2 windows 10
-
- Posts: 54
- Joined: Sun Sep 04, 2016 9:40 pm
Re: Quantity/size grid, vertical to horizontal and join
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
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
openoffice 4.1.2 windows 10
Re: Quantity/size grid, vertical to horizontal and join
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
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
Sliderulesliderule wrote:
Since your SKU content is so convoluted, it will be up to you to further explore how to handle it.
-
- Posts: 54
- Joined: Sun Sep 04, 2016 9:40 pm
Re: Quantity/size grid, vertical to horizontal and join
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" ?
by "calculated output" do you mean "-Medium" & "00-Small" ?
openoffice 4.1.2 windows 10
Re: Quantity/size grid, vertical to horizontal and join
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.cartoonjazz wrote: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
-
- Posts: 54
- Joined: Sun Sep 04, 2016 9:40 pm
Re: Quantity/size grid, vertical to horizontal and join
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.
"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.
openoffice 4.1.2 windows 10
Re: Quantity/size grid, vertical to horizontal and join
Try the following code:cartoonjazz wrote:Code: Select all
"SUBSTR( "SKU", LOCATE( 'Size', "SKU" ) + 4 + 1 )"
Code: Select all
SUBSTR( "SKU", CASE WHEN LOCATE('Size', "SKU") = 0 THEN LOCATE('-', "SKU") + 1 ELSE LOCATE( 'Size', "SKU" ) + 4 + 1 END )
http://www.hsqldb.org/doc/1.8/guide/ch09.html#N1251E
Sliderule
-
- Posts: 54
- Joined: Sun Sep 04, 2016 9:40 pm
Re: Quantity/size grid, vertical to horizontal and join
will look into that, works great thanks much
openoffice 4.1.2 windows 10
Re: Quantity/size grid, vertical to horizontal and join
Since, your original question is resolved, as asked in my first 3 posts to you here, please ( and thank-you very much ) add:cartoonjazz wrote:
will look into that, works great thanks much
Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.