Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi there fellow Qlik Sense users,
I'm trying to get the following result in a pivot table.
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.
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.
=count(distinct If(ChairPartItem <> ChairPartItem2, Ordernumber))
I've added a QVF with an inline table with the example data.
Thanks in advance!
Sebb
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;
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;
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:
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
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?
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.
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...😉
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.
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
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)
=aggr(Sum(Quantity), PartItem, AssembledItem)
Any idea why?
Kind regards,
Sebb.