Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

appi_priya
New Contributor

"Where" Clause

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;

9 Replies
MVP
MVP

Re: "Where" Clause

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

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

sudeepkm
Valued Contributor III

Re: "Where" Clause

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)';

qlikrajan
Contributor III

Re: "Where" Clause

Try this:

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

sasiparupudi1
Honored Contributor III

Re: "Where" Clause

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

one example using the proposed solutions:

QlikCommunity_Thread_179828_Pic1.JPG

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'

or

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

as well.

hope this helps

regards

Marco

Re: "Where" Clause

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

appi_priya
New Contributor

Re: "Where" Clause

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

appi_priya
New Contributor

Re: "Where" Clause

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

Community Browser