"Where" Clause

[appi_priya]

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;

[Oleg_Troyansky]

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

[MarcoWedel]

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]

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

[Anonymous]

Try this:

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

[sasiparupudi1]

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

[MarcoWedel]

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'

or

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

as well.

hope this helps

regards

Marco

[MarcoWedel]

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]

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

[appi_priya]

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