Forum Discussion
1 row generating several rows if the article is a package.
Hi there!
I have a sales table built up of 4 rows and i need to generate a report that shows how many products are sold daily of every kind
This is usually a fairly easy operation, but some articles are packages made up of several items or parts of an item.
I am in need of 1 row generating several rows if the ProductID contains "bib" articles.
Salestable:
| Date | ProductID | Qty | Productname |
| 21.02.2020 | bib1055 | 1 | Pack1 |
| 21.02.2020 | 11936 | 1 | Item 1 |
| 21.02.2020 | bib1115 | 1 | Pack2 |
| 21.02.2020 | 00869 | 1 | item 1 |
| 20.02.2020 | 00867 | 1 | item 2 |
| 21.02.2020 | 11934 | 1 | item 3 |
The packs are built like:
bib1055 = 0,5 x 11936
bib1115 = 0,5 x 11934
bib1046 = 1 x 00869, 1 x 00867, 1 x 00875, 1 x 00879, 1 x 11483, 00841
etc.
Hope someone has time to help me out with this!
1 Reply
- BA_Pete
Super User
Hi Matrabsort ,
I'm assuming you have a 'package components' table that has a list of all individual items contained in a specific package/product ID.
You should left outer merge your package components table onto your sales table on:
salesTable[ProductID] = packageComponents[ProductID].
Once you expand the merge retaining packageComponents[ItemID], you can create a further column that combines the packaged items and the individual items into a single list, something like this:
if [packageComponents.ItemID] = null then [ProductID] else [packageComponents.ItemID]Hope this makes sense.
Pete
