Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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
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
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)';
Try this:
where [ETA Date]>= 01/01/2015 and [ATA Date]>= 01/01/2015 and ([PO Number]<= 9000000000 or [PO Number]>= 9999999999);
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);
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
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
Thanks Oleg I had missed the quotes as the dates were in "proper" Qlikview format.
Thank you Marco for a deeper insight. I got the expected outcome.