topic Re: Eliminate values with compare dates in QlikView

Eliminate values with compare dates

Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I want to load the data where Date2 should not be lesser than Date1.

From the below table, I want to eliminate P1 and P5 as Date2 values are lesser than Date1.

How to achieve? Thanks in advance.

Data:

Load * inline [

Product Date1 Date2

P1 2014 1-Jan-13

P2 2015 2-Jan-15

P3 2016 3-Jan-16

P4 2016 4-Jan-16

P5 2015 5-Jan-14

P6 2015 6-Jan-16

P7 2015 7-Jan-16

P8 2015 8-Jan-16

P9 2015 9-Jan-16

P10 2015 10-Jan-16

];

Re: Eliminate values with compare dates

Anil_Babu_Samineni — Fri, 02 Dec 2016 03:22:14 GMT

DAte1 you are showing only YEar, Do you want to do in script bu using Iterno()

CAn you confirm this?

Re: Eliminate values with compare dates

tamilarasu — Fri, 02 Dec 2016 03:28:16 GMT

Hi Jack,

Have a look at the attached file.

Data:
Load *, If(Date1 <= Date(Date2,'YYYY'),1,0) as CompFlag;
LOAD Product,
Date(Date#(Date1,'YYYY'),'YYYY') as Date1,
Date(Date#(Date2,'DD-MMM-YY'),'DD-MMM-YYYY') as Date2
FROM
Book.xlsx
(ooxml, embedded labels, table is Sheet1);

NoConcatenate
Final:
Load *
Resident Data Where CompFlag =1;

DROP Table Data;

Result: