Number of Product Changes over Time

[Stefan_Weber]

Hello QlikView Community,

I have been looking through the topics but haven´t found one that could help me solve the problem that I am having.

I got asked by our Supply Chain to give them an overview of the number of product changes we are having in our production line(s) over a time period (Day/Month/Quarter/Year)

I have the infrmation from the productionlines which product was produced at which time. I am now looking for a formula, which helps me to automatically calculate this for all of the lines.

I will attach an Excel file with the information that I have already loaded into Qlikview.

AssetKey = ProductionLine (in the Excel File I just loaded one line, but we have a couple of them)

PVKey = the produced Product, in case the cell is empty we did not produce anything and had a downtime

ProductionSegmentStart = Starttime of Production

ProductiosegmentEnd = ENdtime of Production

I am now trying to calculate the number of product changes. I want to ignore the downtimes in this alulation. I manually added a column with the expected result.

For further clarification I will try to put it to words.

If I produce Product A have then a downtime and produce Product A again I don´t want to count this as Product change

If I produce Product A and immediately produce product A again I also don´t want to count this as Product change

If I produce Product A have then a downtime and produce Product B I want to count this as one product change

If I produce Product A and immediately produce product B again I  want to count this as Product change

I hope I explained this in a way, so that you can help me.

Thank you in advance.

Stefan

[MindaugasBacius]

I tried to achieve  the results you've written in excel.

I got quite close result. I guess I do miss some logic here:

The script to play with:

tmp:
Directory;
LOAD AssetKey, 
     ProductionSegmentID, 
     PVKey, 
     ProductionSegmentStart, 
     ProductionSegmentEnd, 
     Duration, 
     [product Change]
FROM
[..\Downloads\Product_Change_Example.xls]
(biff, embedded labels, table is Sheet1$);


NoConcatenate
tmp2:
LOAD *
Resident tmp
Order By ProductionSegmentEnd asc;

DROP Table tmp;


NoConcatenate
tmp:
LOAD *
	,If(PVKey = Peek(PVKey) or Len(Trim(PVKey)) = 0, 0, 1) as test
Resident tmp2;

DROP Table tmp2;

[sunny_talwar]

One option you can try

Table:
LOAD RowNo() as RowNum,
	 AssetKey, 
     ProductionSegmentID, 
     PVKey, 
     ProductionSegmentStart, 
     ProductionSegmentEnd, 
     Duration, 
     [product Change]
FROM
[..\..\Downloads\Product_Change_Example.xls]
(biff, embedded labels, table is Sheet1$);

FinalTable:
LOAD *,
	 If(Len(Trim(Peek(PVKey))) = 0, Peek(PreviousPVKey), Peek(PVKey)) as PreviousPVKey,
	 If(PVKey = If(Len(Trim(Peek(PVKey))) = 0, Peek(PreviousPVKey), Peek(PVKey)) or Len(Trim(PVKey)) = 0 or Len(Trim(If(Len(Trim(Peek(PVKey))) = 0, Peek(PreviousPVKey), Peek(PVKey)))) = 0, 0, 1) as Change
Resident Table
Order By ProductionSegmentEnd asc;

DROP Table Table;

[Stefan_Weber]

Hello Sunny,

thanks for your reply.

How would this work if I had more than one AssetKey in the list? Would I order by Assetkey first and then ProductionSegmentEnd?

How is the peek statement working depending on the sort order ?

What do you need the rowNo for ?

Thanks for your input

Highly appreciated.

Stefan

[sunny_talwar · Accepted solution]

---

@Stefan_Weber wrote:

How would this work if I had more than one AssetKey in the list? Would I order by Assetkey first and then ProductionSegmentEnd?

---

This is correct, you will probably need to first sort for AssetKey and then ProductionSegmentEnd. But if you don't want the Peek function to continue from one AssetKey to another... you might also need to add If(AssetKey = Previous(AssetKey), theExistingPeekStatement) as ....

---

@Stefan_Weber wrote:

How is the peek statement working depending on the sort order ?

---

Yes, you are right. Peek/Previous depend on the sort order.

---

@Stefan_Weber wrote:

What do you need the rowNo for ?

---

I added it for myself to view the data sorted on the front end. You probably don't need it 🙂

[Stefan_Weber]

Hello Sunny,

again thank you for your help. With the last info I managed to get it running.

Thank you all for your contribution and help.

Stefan