topic Re: "Where" Clause in QlikView

"Where" Clause

appi_priya — Wed, 09 Sep 2015 16:49:50 GMT

Hi,

Need help in reducing the file size by applying the following extraction criteria:

1. PO number is less than "9000000000" greater than "9999999999".

2. Field [ETA Date] is >= 01/01/2015, or Field [ATA DATE] is >= 01/01/2015

I need to reduce the number of records in the output.

I am currently giving the condition as shown in the table below but I am not getting the expected outcome:

The only outcome that is satisfied is the "PO number is less than "9000000000" greater than "9999999999" but the second condition is not met. Let me know if I missed anything.

Test:

LOAD [PO Number],

[PO Item Number] ,

[Delivery Number] ,

[Delivery Item] ,

[Shipment Number] ,

[ETA Date] ,

[ATA Date] ,

Resident Delivery

where [ETA Date]>= 01/01/2015 and [ATA Date]>= 01/01/2015

and [PO Number]<= 9000000000 or [PO Number]>= 9999999999;

Re: "Where" Clause

Oleg_Troyansky — Wed, 09 Sep 2015 16:55:09 GMT

If your date fields are formatted as "proper" QlikView (or Microsoft) dates, then you should enclose your date value in single quotes:

where [ETA Date]>= '01/01/2015' and [ATA Date]>= '01/01/2015'

Otherwise, you may need to transform your dates into the proper format.

cheers,

Oleg Troyansky

QlikView Your Business: An expert guide to Business Discovery with QlikView and Qlik Sense

Re: "Where" Clause

MarcoWedel — Wed, 09 Sep 2015 17:55:03 GMT

you might also need some brackets here:

([PO Number]<= 9000000000 or [PO Number]>= 9999999999);

to get the correct logic as 'and' has a higher priority than 'or'

regards

Marco

Re: "Where" Clause

sudeepkm — Wed, 09 Sep 2015 18:01:02 GMT

I think it would be wise to declare the date in a variable and then use the variable in your script.

let vDate = <yourdate>;

[ETA Date] is >= '$(vDate)';

Re: "Where" Clause

Anonymous — Wed, 09 Sep 2015 18:20:53 GMT

Try this:

where [ETA Date]>= 01/01/2015 and [ATA Date]>= 01/01/2015 and ([PO Number]<= 9000000000 or [PO Number]>= 9999999999);

Re: "Where" Clause

sasiparupudi1 — Wed, 09 Sep 2015 19:54:56 GMT

Test:

LOAD [PO Number],

[PO Item Number] ,

[Delivery Number] ,

[Delivery Item] ,

[Shipment Number] ,

[ETA Date] ,

[ATA Date] ,

Resident Delivery

where ([ETA Date]>= '01/01/2015' and [ATA Date]>= '01/01/2015')

and ([PO Number]<= 9000000000 or [PO Number]>= 9999999999);

Re: "Where" Clause

MarcoWedel — Wed, 09 Sep 2015 21:36:12 GMT

one example using the proposed solutions:

Test:
LOAD [PO Number],
    [ETA Date],
    [ATA Date]
Inline [
    PO Number, ETA Date, ATA Date
    8999999997, 01/01/2015, 01/01/2015
    8999999998, 12/31/2014, 01/02/2015
    8999999999, 01/02/2015, 12/31/2014
    9000000000, 01/02/2015, 01/02/2015
    9000000001, 01/02/2015, 01/02/2015
    9000000002, 01/02/2015, 01/02/2015
    9000000003, 01/02/2015, 01/02/2015
    9999999998, 01/02/2015, 01/02/2015
    9999999999, 01/02/2015, 01/02/2015
    10000000000, 01/02/2015, 01/02/2015
    10000000001, 12/31/2014, 01/02/2015
    10000000002, 01/02/2015, 12/31/2014
    10000000003, 12/31/2014, 12/31/2014
]
Where [ETA Date]>= '01/01/2015' and [ATA Date]>= '01/01/2015'
  and ([PO Number]<= 9000000000 or [PO Number]>= 9999999999);

other expressions for

[ETA Date]>= '01/01/2015' and [ATA Date]>= '01/01/2015'

could be

RangeMin([ETA Date],[ATA Date])>= '01/01/2015'

Year(RangeMin([ETA Date],[ATA Date]))>= 2015

as well.

hope this helps

regards

Marco

Re: "Where" Clause

MarcoWedel — Wed, 09 Sep 2015 21:45:10 GMT

if 'MM/DD/YYYY' is not your default DateFormat, then it might be necessary to interpret your date string like

Where [ETA Date]>= Date#('01/01/2015','MM/DD/YYYY') and [ATA Date]>= Date#('01/01/2015','MM/DD/YYYY')

Test:
LOAD *
Where [ETA Date]>= Date#('01/01/2015','MM/DD/YYYY') and [ATA Date]>= Date#('01/01/2015','MM/DD/YYYY')
  and ([PO Number]<= 9000000000 or [PO Number]>= 9999999999);
LOAD [PO Number],
    Date#([ETA Date],'MM/DD/YYYY') as [ETA Date],
    Date#([ATA Date],'MM/DD/YYYY') as [ATA Date]
Inline [
    PO Number, ETA Date, ATA Date
    8999999997, 01/01/2015, 01/01/2015
    8999999998, 12/31/2014, 01/02/2015
    8999999999, 01/02/2015, 12/31/2014
    9000000000, 01/02/2015, 01/02/2015
    9000000001, 01/02/2015, 01/02/2015
    9000000002, 01/02/2015, 01/02/2015
    9000000003, 01/02/2015, 01/02/2015
    9999999998, 01/02/2015, 01/02/2015
    9999999999, 01/02/2015, 01/02/2015
    10000000000, 01/02/2015, 01/02/2015
    10000000001, 12/31/2014, 01/02/2015
    10000000002, 01/02/2015, 12/31/2014
    10000000003, 12/31/2014, 12/31/2014
];

hope this helps

regards

Marco

Re: "Where" Clause

appi_priya — Thu, 10 Sep 2015 09:09:24 GMT

Thanks Oleg I had missed the quotes as the dates were in "proper" Qlikview format.

Re: "Where" Clause

appi_priya — Thu, 10 Sep 2015 09:10:59 GMT

Thank you Marco for a deeper insight. I got the expected outcome.