Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
tan_chungkam
Contributor

To show new order

Hi fellas,

I have 2 excel files which are May and June sales report.

The table in each file basically look like as below shown.

I would like to create a column which shows the order whether it is a new order or existing order.

Thus, i looking for a formula / function - if the order no. appear in last month then it is an existing order. if the order no. do not appear in last month then it is a new order.

Is there any function to do that?

Month

Order no.

Order Type

Delivery week

Jan

1111111

New Order

201803

Jan

2222222

New Order

201805

Jan

3333333

New Order

201804

Jan

4444444

New Order

201811

Feb

2222222

Existing Order

201808

Feb

4444444

Existing Order

201811

Feb

5555555

New Order

201812

Mar

4444444

Existing Order

201815

Mar

5555555

Existing Order

201815

Mar

6666666

New Order

201818

Thanks.

1 Solution

Accepted Solutions
Highlighted
mehdi_jarboui
New Contributor

Re: To show new order

hello

maybe something like that

load

*,

if(previous([Order no.])<>[Order no.],'New','existing order') as NE

resident sales_report

ORDER BY [Order no.];

View solution in original post

6 Replies
Highlighted
mehdi_jarboui
New Contributor

Re: To show new order

hello

maybe something like that

load

*,

if(previous([Order no.])<>[Order no.],'New','existing order') as NE

resident sales_report

ORDER BY [Order no.];

View solution in original post

Highlighted
olivierrobin
Valued Contributor III

Re: To show new order

hello

load first month

concatenate 2nd month

add a column

if(exists(Order no),1,0) as flag_exist

Highlighted
mehdi_jarboui
New Contributor

Re: To show new order

hello

maybe something like that

load

*,

if(previous([Order no.])<>[Order no.],'New','existing order') as NE

resident sales_report

ORDER BY [Order no.],Month;

Highlighted
rajeshqvd
Contributor II

Re: To show new order

if(exists([Order no.]),'Exit Order','New Order') as [Order Type]

Highlighted
tan_chungkam
Contributor

Re: To show new order

hi rajeshqvd

where should i put the formula?

should i put the expression into the 'add a column by using measure'?

or should i put it into the data load editor?

Really appreciate your help.

Highlighted
rajeshqvd
Contributor II

Re: To show new order

Yes put it into the data load editor