Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
SBDataspark
Creator
Creator

Count combination of sales for parts of bom item

Hi there fellow Qlik Sense users,

I'm trying to get the following result in a pivot table.

pivot_table.png

This shows me the count of sales orders in which a combination of the different seats and frames of assembled chairs are sold.
For example seat "Seat Grey" has been sold in two sales orders in combination with "Frame A - Black Slide".
And hase been sold once in combination with the "Frame B - Black Cross", etc.

The underlying sales order lines are cut back to a couple essential columns to support this question.

order_lines.png

The "LineNumber" might look a bit strange, but that's because the source of this table is a join between the sales order lines table which contains the assembeld items and the bill of materials table that holds the link between the assembled item and the parts.
So in the original sales order lines table the "LineNumber" only contains the "AssembledChairItem" and thus is unique per "OrderNumber.

The load script:

[OrderLines]:
LOAD * Inline [
Ordernumber,LineNumber,AssembledChairItem,ChairPartItem,Quantity
11,1,Chair - Grey Cross,Frame B - Black Cross,1
11,1,Chair - Grey Cross,Seat Grey,1
11,2,Chair - Grey Slide,Frame A - Black Slide,1
11,2,Chair - Grey Slide,Seat Grey,1
12,1,Chair - Yellow Slide,Frame A - Black Slide,2
12,1,Chair - Yellow Slide,Seat Yellow,2
12,2,Chair - Dark Grey Slide,Frame A - Black Slide,2
12,2,Chair - Dark Grey Slide,Seat Dark Grey,2
12,3,Chair - Grey Slide,Frame A - Black Slide,2
12,3,Chair - Grey Slide,Seat Grey,2
13,1,Chair - Dark Grey Turn,Frame C - Black Turn,4
13,1,Chair - Dark Grey Turn,Seat Dark Grey,4
13,2,Chair - Yellow Turn,Frame C - Black Turn,2
13,2,Chair - Yellow Turn,Seat Yellow,2
];

Load
[Ordernumber],
[ChairPartItem] as [ChairPartItem2]
Resident [OrderLines];

The dimension of the pivot is ChairPartItem and the column of the pivot table is ChairPartItem2.

At the moment I'm not able to get a pivot table as shown above in my Excel example.
I've tried all sorts of count and aggregate combinations in the measurement, but don't seem to get it right.

pivot_table_qlik.png

=count(distinct If(ChairPartItem <> ChairPartItem2, Ordernumber))

I've added a QVF with an inline table with the example data.

Thanks in advance!

Sebb

1 Solution

Accepted Solutions
rubenmarin

Hi, try with this script, at least to have one row by order line with seat and Frame:

[OrderLines]:
LOAD *,[Ordernumber]&LineNumber as OrderLine Inline [
Ordernumber,LineNumber,AssembledChairItem,ChairPartItem,Quantity
11,1,Chair - Grey Cross,Frame B - Black Cross,1
11,1,Chair - Grey Cross,Seat Grey,1
11,2,Chair - Grey Slide,Frame A - Black Slide,1
11,2,Chair - Grey Slide,Seat Grey,1
12,1,Chair - Yellow Slide,Frame A - Black Slide,2
12,1,Chair - Yellow Slide,Seat Yellow,2
12,2,Chair - Dark Grey Slide,Frame A - Black Slide,2
12,2,Chair - Dark Grey Slide,Seat Dark Grey,2
12,3,Chair - Grey Slide,Frame A - Black Slide,2
12,3,Chair - Grey Slide,Seat Grey,2
13,1,Chair - Dark Grey Turn,Frame C - Black Turn,4
13,1,Chair - Dark Grey Turn,Seat Dark Grey,4
13,2,Chair - Yellow Turn,Frame C - Black Turn,2
13,2,Chair - Yellow Turn,Seat Yellow,2
];

tmpCount:
Load
[Ordernumber]&LineNumber as OrderLine,
If(Left([ChairPartItem],4)='Seat', [ChairPartItem]) as Seat,
If(Left([ChairPartItem],5)='Frame', [ChairPartItem]) as Frame
Resident [OrderLines];

Count:
NoConcatenate LOAD 
	OrderLine, 
	MinString(Seat) as Seat,
    MinString(Frame) as Frame
Resident tmpCount
Group by OrderLine;

DROP Table tmpCount;

View solution in original post

8 Replies
rubenmarin

Hi, try with this script, at least to have one row by order line with seat and Frame:

[OrderLines]:
LOAD *,[Ordernumber]&LineNumber as OrderLine Inline [
Ordernumber,LineNumber,AssembledChairItem,ChairPartItem,Quantity
11,1,Chair - Grey Cross,Frame B - Black Cross,1
11,1,Chair - Grey Cross,Seat Grey,1
11,2,Chair - Grey Slide,Frame A - Black Slide,1
11,2,Chair - Grey Slide,Seat Grey,1
12,1,Chair - Yellow Slide,Frame A - Black Slide,2
12,1,Chair - Yellow Slide,Seat Yellow,2
12,2,Chair - Dark Grey Slide,Frame A - Black Slide,2
12,2,Chair - Dark Grey Slide,Seat Dark Grey,2
12,3,Chair - Grey Slide,Frame A - Black Slide,2
12,3,Chair - Grey Slide,Seat Grey,2
13,1,Chair - Dark Grey Turn,Frame C - Black Turn,4
13,1,Chair - Dark Grey Turn,Seat Dark Grey,4
13,2,Chair - Yellow Turn,Frame C - Black Turn,2
13,2,Chair - Yellow Turn,Seat Yellow,2
];

tmpCount:
Load
[Ordernumber]&LineNumber as OrderLine,
If(Left([ChairPartItem],4)='Seat', [ChairPartItem]) as Seat,
If(Left([ChairPartItem],5)='Frame', [ChairPartItem]) as Frame
Resident [OrderLines];

Count:
NoConcatenate LOAD 
	OrderLine, 
	MinString(Seat) as Seat,
    MinString(Frame) as Frame
Resident tmpCount
Group by OrderLine;

DROP Table tmpCount;
SBDataspark
Creator
Creator
Author

Hi rubenmarin,

Thank you for taking the time to respond to my question. Really appreciate it!

I've run your script and this does what I need when I put the "Seat" and "Frames" dimensions in the pivot table.

Thanks for that!

Now, I'm trying to figure out what you're doing and why...😉

If I break it down into parts you're doing the following:

  1. Table tmpCount retrieves the combination of OrderNumber and LineNumber with a selection of items from the SalesOrderLines and mark them as either a seat or a frame. Am I right? Now you're doing that based on the description of the items. That's because I sized down the data model to make it easier to understand for all of us.
    But if I have another characteristic to seperate the different types of items, like an item group or an assortment in a "Items" table I'd be able to fill this tmpCount table based on that. Am I right?

    tmpCount.png

    Now this is just for the chairs in the current snapshot of the "OrderLines".
    Let's say the "OrderLines" table consists of more than just sales of chairs. For example also tables with frames and tabletops and other assembled items.
    For the sake of it let's just assume for now there are always two parts in an assembled item.
    Then I rename the columns 'Seat' and 'Frame' to 'Part 1' and 'Part 2' to make it more general.
    And somehow add a "PartLineNumber" to the "OrderLines" so that I can select on that to fill "Part 1 " and "Part 2"?
    Before doing that I need to sort the "PartItem" lines per order line first to make sure that the same item will always have the same "PartLineNumber" in every order where it's present. Am I right with this?
  2. With the final table Count you're retrieving the lines from tmpCount and sort of compressing the records with the same OrderNumber/LineNumber combination together.

    Count.png

  3. The measurement of the pivot table still is "=count(distinct OrderNumber)" because the tables OrderLines and Count are linked by OrderLine  being OrderNumber&LineNumber?

Now I'm trying to understand why...😁

That I haven't figured out yet...

Why do you need to create a separate table with the combinations and is it not possible to do this only based on the OrderLines table?

Thanks in advance,

Sebb

SBDataspark
Creator
Creator
Author

I managed to add the AutoNumber for "ChairPartItem" to my load script so that you'd be able to use it for any assembled item with two parts.

In my actual app with a more detailed data model I had to sort the "ChairPartItem" lines by "ItemCode" first.
This to make sure that the same "ChairPartItem" will have the same "PartLineNumber" in every order.
So that the ChairPartItem will always be "Part1" or "Part2" in the "Count" table and they won't show up in both axes of the pivot table.

The load script looks like this.

[OrderLines]:
LOAD *
,[Ordernumber]&LineNumber as OrderLine
, AutoNumber([AssembledItem]&[PartItem],[AssembledItem]) as PartLineNumber
Inline [ Ordernumber,LineNumber,AssembledItem,PartItem,Quantity
11,1,Chair - Grey Cross,Frame B - Black Cross,1
11,1,Chair - Grey Cross,Seat Grey,1
11,2,Chair - Grey Slide,Frame A - Black Slide,1
11,2,Chair - Grey Slide,Seat Grey,1
11,3,Table - Oval Cross,Frame D - Black Cross,1
11,3,Table - Oval Cross,Tabletop Oval,1
12,1,Chair - Yellow Slide,Frame A - Black Slide,2
12,1,Chair - Yellow Slide,Seat Yellow,2
12,2,Chair - Dark Grey Slide,Frame A - Black Slide,2
12,2,Chair - Dark Grey Slide,Seat Dark Grey,2
12,3,Chair - Grey Slide,Frame A - Black Slide,2
12,3,Chair - Grey Slide,Seat Grey,2
12,4,Table - Round Cross,Frame D - Black Cross,1
12,4,Table - Round Cross,Tabletop Round,1
13,1,Chair - Dark Grey Turn,Frame C - Black Turn,4
13,1,Chair - Dark Grey Turn,Seat Dark Grey,4
13,2,Chair - Yellow Turn,Frame C - Black Turn,2
13,2,Chair - Yellow Turn,Seat Yellow,2
13,3,Table - Square Cross,Frame D - Black Cross,1
13,3,Table - Square Cross,Tabletop Square,1
];

tmpCount: Load [Ordernumber]&LineNumber as OrderLine
, If(PartLineNumber = 1, [PartItem]) as [Part1]
, If(PartLineNumber = 2, [PartItem]) as [Part2]
Resident [OrderLines];

Count: NoConcatenate
LOAD OrderLine
, MinString(Part1) as Part1
, MinString(Part2) as Part2
Resident tmpCount
Group by OrderLine;

DROP Table tmpCount;

@rubenmarin Thanks again for your answer!!

Maybe you can shed your light on why you should do it this way?

rubenmarin

I haven't been aware of your first answer... I'll try to explain in a couple of hours when I have a free spot.

Regards.

SBDataspark
Creator
Creator
Author

No worries!! I'm not in a hurry.
I managed to finish my pivot table with part combinations based on your input and I'm very grateful for that!

The additional feedback is just to learn from your insights...😉

rubenmarin

Hi @SBDataspark, trying to explain the steps...

The tmpCount table it's only to split PartItem in two different fields and OrderLine is used to identify all fields that are part of the same assembled item.

This can be done in many different ways, in example in the first table, if all records are sorted by OrderNumber an LineNumber you can use If(Peek(OrderNumber)=OrderNumber and Peek(LineNumber)=LineNumber, Peek(PartNumber)+1, 1), so the first record has value '1', the next is '2'... and the next Order and line diffrent starts again from 1.

The Count table just merges the rows by OrderLine, to have both fields in the same record.

In expression the use of Count(distinct) is because the original table has 2 records for each OrderLine, so it's counted twice, if you don't need this first table you can do a DROP Table and then the 'distinct' is not neccesary.

SBDataspark
Creator
Creator
Author

Hi Rubenmarin,

Thanks for the explanation on the how. I really appreciate it!!👍
Can you maybe elaborate a bit on how you came to think up this solution?

Kind regards,

Sebb

SBDataspark
Creator
Creator
Author

Dear fellow Qlik Sense Users,

I've managed to get this to work with the help of Rubenmarin.
And I have an additional question related to this.

At the moment I'm counting the orders in which the part items are combined based on the assembeled item.
I changed the pivot table to show the quantity of parts as well.

Now I'd like to show totals both the rows as wel as the columns.

In the "count the orders" pivot this works perfectly, but when I show the totals in the aggr sum pivot this doesn't work.

I included the new example app with this reply.

This is the load script.

[OrderLines]:
LOAD *
,[Ordernumber]&LineNumber as OrderLine
, AutoNumber([AssembledItem]&[PartItem],[AssembledItem]) as PartLineNumber
Inline [ Ordernumber,LineNumber,AssembledItem,PartItem,Quantity
11,1,Chair - Grey Cross,Frame B - Black Cross,1
11,1,Chair - Grey Cross,Seat Grey,1
11,2,Chair - Grey Slide,Frame A - Black Slide,1
11,2,Chair - Grey Slide,Seat Grey,1
11,3,Table - Oval Cross,Frame D - Black Cross,1
11,3,Table - Oval Cross,Tabletop Oval,1
12,1,Chair - Yellow Slide,Frame A - Black Slide,2
12,1,Chair - Yellow Slide,Seat Yellow,2
12,2,Chair - Dark Grey Slide,Frame A - Black Slide,2
12,2,Chair - Dark Grey Slide,Seat Dark Grey,2
12,3,Chair - Grey Slide,Frame A - Black Slide,2
12,3,Chair - Grey Slide,Seat Grey,2
12,4,Table - Round Cross,Frame D - Black Cross,1
12,4,Table - Round Cross,Tabletop Round,1
13,1,Chair - Dark Grey Turn,Frame C - Black Turn,4
13,1,Chair - Dark Grey Turn,Seat Dark Grey,4
13,2,Chair - Yellow Turn,Frame C - Black Turn,2
13,2,Chair - Yellow Turn,Seat Yellow,2
13,3,Table - Square Cross,Frame D - Black Cross,1
13,3,Table - Square Cross,Tabletop Square,1
];

tmpCount: Load [Ordernumber]&LineNumber as OrderLine
, If(PartLineNumber = 1, [PartItem]) as [Part1]
, If(PartLineNumber = 2, [PartItem]) as [Part2]
Resident [OrderLines];

Count: NoConcatenate
LOAD OrderLine
, MinString(Part1) as Part1
, MinString(Part2) as Part2
Resident tmpCount
Group by OrderLine;

DROP Table tmpCount;

And in the pivot table I'm using the following measures.

=count(distinct OrderLine)

Sebb_0-1605110141876.png

=aggr(Sum(Quantity), PartItem, AssembledItem)

Sebb_2-1605111864821.png

Any idea why?

Kind regards,

Sebb.