topic Re: Creating a new order number in QlikView

Creating a new order number

tiklabaq — Tue, 04 Jul 2017 06:47:30 GMT

Hi Friends,

we got many data from our machines and we want to make a report.

I have a table for example with this data:

Timestamp	Program Number (is specific for a Material and not a order number)
20.08.2015 06:00:00	64
20.08.2015 06:10:00	64
20.08.2015 07:00:00	64
20.08.2015 07:30:00	64
...
21.08.2015 01:00:00	64
21.08.2015 01:10:00	22
21.08.2015 01:30:00	22
...	22
21.08.2015 10:10:00	22
...
30.08.2015 14:10:00	64
...

We want now to see, which productions are in the same order number and which are new.

The new table should be like this: Date is the first time when this Order started:

Date	Program Number (is specific for a Material and not a order number)	Order Number
20.08.2015	64	1
21.08.2015	22	2
30.08.2015	64	3

Date

Program Number

(is specific for a Material and not a order number)

Order Number

20.08.2015

21.08.2015

30.08.2015

How can I do this? I have tried it with the peek function, but it does not work.

Thx

Re: Creating a new order number

Anil_Babu_Samineni — Tue, 04 Jul 2017 06:51:10 GMT

This looks like you need Rowno() and Min(ProgramNo) for expressions?

Re: Creating a new order number

lironbaram — Tue, 04 Jul 2017 06:56:18 GMT

if i understood your request it should be something like this

after loading the table will call it DataTemp

your script should be something like this

Data:

Load *,

if(previous([Program Number])=Program Number,peek('[Order Number]'),Rangesum(peek('[Order Number]'),1)) As [Order Number]

resident DataTemp

order by Date;

drop table Data

you need to use resident, so you'll be able to use Order by

Re: Creating a new order number

arvind_patil — Tue, 04 Jul 2017 06:56:50 GMT

Hi Ibrahim,

Use Timestamp# Function and load distinct records you may get output.

Thanks,

Arvind Patil

Re: Creating a new order number

PrashantSangle — Tue, 04 Jul 2017 06:57:10 GMT

Hi,

Try Using floor() to make one date then using firstsortedValue() you can use first value from table.

For details search in community or help menu.

Regards,

Prashant

Re: Creating a new order number

tiklabaq — Tue, 04 Jul 2017 07:27:42 GMT

Thank you very much Liron, this is gooood

Is the 1 after the , for the first Row in the table? If that's true, then I've understood it

And thanks to all the others who have replied. You are great!