Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Select the first process from process step on a job.

This is a difficult one to explain, here goes. Ordernumber (first column) has different steps ie more than one task, i want to only have during the load or in set analysis the first step which is identified by the prosequence (column 5 below).

ie ordernumber 168599 has two step 1250 and 1150 these number can can but the prosequence column is indexing based on process, i want to only show for 168599 below, and the first or lowest number in prosequence for each ordernumber . so each ordernumber would only show one record and that would be the lowest prosequence number for that ordernumber.

thanks in advance Brett

ordernumber Description StepNum processtype prosequence sum(quantity)
168599Primaflex Printer (W&H)1150PRT309675.69

hha.png

1 Solution

Accepted Solutions
Highlighted

Re: Select the first process from process step on a job.

Hi,

one solution could be also:

QlikCommunity_Thread_164229_Pic1.JPG

table1:

LOAD * INLINE [

    ordernumber, Description, StepNum, processtype, prosequence

    168599, Cobra Sleever, 1250, FIN, 50

    168599, Primaflex Printer, 1150, PRT, 30

    168606, ASHE Slitter 1, 1250, SLT, 30

    168606, Miraflex 2 Printer, 1150, FIN, 40

    168606, Cobra Sleever, 1250, PRT, 20

    168606, Primaflex Printer, 1150, SLT, 50

    168653, ASHE Slitter 1, 1250, FIN, 10

    168653, Miraflex 2 Printer, 1150, SLT, 40

    168653, Primaflex Printer, 1250, PRT, 30

];

Right Join (table1)

LOAD ordernumber,

    Min(prosequence) as prosequence

Resident table1

Group By ordernumber;

hope this helps

regards

Marco

View solution in original post

4 Replies
Highlighted
MVP
MVP

Re: Select the first process from process step on a job.

small example, bold the most important 

a:

LOAD * inline [

ordernumber,      Description,      StepNum,      processtype,      prosequence,     quantity)

168599, cobra, 1250, FIN, 50, 19175

168599, primaflex, 1150, PRT, 30, 19675

168606, aa, 1250, SLT, 40, 6169

168606, bb, 1150, PRT, 30, 6270

168653, ccc, 1250, SLT, 40, 14668

168653, ddd, 1150, PRT, 30, 14768

]

;

b:

NoConcatenate

load *

resident a

where peek(ordernumber) <> ordernumber

order by ordernumber, prosequence;

drop table a;

Highlighted

Re: Select the first process from process step on a job.

Hi,

one solution could be also:

QlikCommunity_Thread_164229_Pic1.JPG

table1:

LOAD * INLINE [

    ordernumber, Description, StepNum, processtype, prosequence

    168599, Cobra Sleever, 1250, FIN, 50

    168599, Primaflex Printer, 1150, PRT, 30

    168606, ASHE Slitter 1, 1250, SLT, 30

    168606, Miraflex 2 Printer, 1150, FIN, 40

    168606, Cobra Sleever, 1250, PRT, 20

    168606, Primaflex Printer, 1150, SLT, 50

    168653, ASHE Slitter 1, 1250, FIN, 10

    168653, Miraflex 2 Printer, 1150, SLT, 40

    168653, Primaflex Printer, 1250, PRT, 30

];

Right Join (table1)

LOAD ordernumber,

    Min(prosequence) as prosequence

Resident table1

Group By ordernumber;

hope this helps

regards

Marco

View solution in original post

Highlighted
Creator
Creator

Re: Select the first process from process step on a job.

Thanks I will test later today and respond, thanks for idea looks good. Cheers Brett

Highlighted
Creator
Creator

Re: Select the first process from process step on a job.

BRILLIANT !!! Thanks Brett