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

Creating tables and queries

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

Postby cartoonjazz » Mon Jul 10, 2017 4:15 pm

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
raw.jpg (17.83 KiB) Viewed 861 times

result
result.jpg
result.jpg (14.18 KiB) Viewed 861 times

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
cartoonjazz
 
Posts: 34
Joined: Sun Sep 04, 2016 9:40 pm

Re: quantity/size grid, vertical to horizontal and join

Postby Sliderule » Mon Jul 10, 2017 7:27 pm

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
(5.57 KiB) Downloaded 45 times

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
User avatar
Sliderule
Volunteer
 
Posts: 1099
Joined: Thu Nov 29, 2007 9:46 am

Re: quantity/size grid, vertical to horizontal and join

Postby cartoonjazz » Mon Jul 10, 2017 9:14 pm

:) 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?
openoffice 4.1.2 windows 10
cartoonjazz
 
Posts: 34
Joined: Sun Sep 04, 2016 9:40 pm

Re: Quantity/size grid, vertical to horizontal and join

Postby Sliderule » Tue Jul 11, 2017 12:19 am

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.
User avatar
Sliderule
Volunteer
 
Posts: 1099
Joined: Thu Nov 29, 2007 9:46 am

Re: Quantity/size grid, vertical to horizontal and join

Postby cartoonjazz » Tue Jul 11, 2017 4:26 pm

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'
openoffice 4.1.2 windows 10
cartoonjazz
 
Posts: 34
Joined: Sun Sep 04, 2016 9:40 pm

Re: Quantity/size grid, vertical to horizontal and join

Postby Sliderule » Tue Jul 11, 2017 4:42 pm

You asked / wrote it as:

Code: Select all   Expand viewCollapse view
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   Expand viewCollapse view
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. :geek:

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
User avatar
Sliderule
Volunteer
 
Posts: 1099
Joined: Thu Nov 29, 2007 9:46 am

Re: Quantity/size grid, vertical to horizontal and join

Postby cartoonjazz » Tue Jul 11, 2017 6:26 pm

exactly what I was looking for that's great thanks again
openoffice 4.1.2 windows 10
cartoonjazz
 
Posts: 34
Joined: Sun Sep 04, 2016 9:40 pm

Re: Quantity/size grid, vertical to horizontal and join

Postby cartoonjazz » Thu Oct 05, 2017 8:13 pm

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
openoffice 4.1.2 windows 10
cartoonjazz
 
Posts: 34
Joined: Sun Sep 04, 2016 9:40 pm

Re: Quantity/size grid, vertical to horizontal and join

Postby Sliderule » Thu Oct 05, 2017 8:24 pm

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

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

Sliderule
User avatar
Sliderule
Volunteer
 
Posts: 1099
Joined: Thu Nov 29, 2007 9:46 am

Re: Quantity/size grid, vertical to horizontal and join

Postby cartoonjazz » Thu Oct 05, 2017 8:39 pm

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" ?
openoffice 4.1.2 windows 10
cartoonjazz
 
Posts: 34
Joined: Sun Sep 04, 2016 9:40 pm

Re: Quantity/size grid, vertical to horizontal and join

Postby Sliderule » Thu Oct 05, 2017 8:51 pm

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" ?

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
User avatar
Sliderule
Volunteer
 
Posts: 1099
Joined: Thu Nov 29, 2007 9:46 am

Re: Quantity/size grid, vertical to horizontal and join

Postby cartoonjazz » Thu Oct 05, 2017 9:19 pm

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.
openoffice 4.1.2 windows 10
cartoonjazz
 
Posts: 34
Joined: Sun Sep 04, 2016 9:40 pm

Re: Quantity/size grid, vertical to horizontal and join

Postby Sliderule » Thu Oct 05, 2017 10:16 pm

cartoonjazz wrote:
Code: Select all   Expand viewCollapse view
"SUBSTR( "SKU", LOCATE( 'Size', "SKU" ) + 4 + 1 )"



Try the following code:

Code: Select all   Expand viewCollapse view
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
User avatar
Sliderule
Volunteer
 
Posts: 1099
Joined: Thu Nov 29, 2007 9:46 am

Re: Quantity/size grid, vertical to horizontal and join

Postby cartoonjazz » Thu Oct 05, 2017 10:34 pm

will look into that, works great thanks much
openoffice 4.1.2 windows 10
cartoonjazz
 
Posts: 34
Joined: Sun Sep 04, 2016 9:40 pm

Re: Quantity/size grid, vertical to horizontal and join

Postby Sliderule » Fri Oct 06, 2017 1:15 am

cartoonjazz wrote:
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 :bravo: ) add:

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
User avatar
Sliderule
Volunteer
 
Posts: 1099
Joined: Thu Nov 29, 2007 9:46 am


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 3 guests