topic Re: Where statement in QlikView

Where statement

nicolai_moller — Wed, 27 Sep 2017 09:15:19 GMT

Hope someone can help me here.

I have a table where i need to find all ID's that have Status = 'In progress' after january 5th 2017, but it also needs to exclude ID's that have Status = 'In progress' before or on january 5th 2017. In the table below, only ID=2 should be returned.

This needs to be done in the load script and preferably in a where statement.

ID	Date	Status
1	01-12-2016	In progress
1	02-01-2017	Waiting
1	10-01-2017	In progress
1	23-01-2017	Done
2	10-01-2017	In progress
2	16-01-2017	Done

Loading the table with this where statement will return both ID's.

load

ID,

Date,

Status

From table

where Date > '05-01-2017' and Status like 'In progress';

Thanks

Re: Where statement

Anonymous — Wed, 27 Sep 2017 09:27:42 GMT

!May be like this:

T1:

LOAD ID,

Date,

Status

FROM

[\Desktop\ex.xls]

(biff, embedded labels, table is Sheet1$)

where Date>'05-01-2017' and Status='In progress' and ID=2;

Output:

ID	Date	Status
2	10-01-2017	In progress

Regards

Tahemas

Re: Where statement

Kushal_Chawda — Wed, 27 Sep 2017 09:33:16 GMT

try this

T1:

LOAD ID,

Date,

Status

FROM

[\Desktop\ex.xls]

(biff, embedded labels, table is Sheet1$)

where date#(Date,'DD-MM-YYYY') > makedate(2017,1,15) and Status='In progress' ;

Re: Where statement

prat1507 — Wed, 27 Sep 2017 09:35:19 GMT

Hi Nicolai

This would be returned

10-01-2017

In progress

10-01-2017

In progress

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='DD-MM-YYYY'; // Kindly mention the format used for dates

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Tab:

LOAD * Inline [

ID, Date, Status

1, 01-12-2016, In progress

1, 02-01-2017, Waiting

1, 10-01-2017, In progress

1, 23-01-2017, Done

2, 10-01-2017, In progress

2, 16-01-2017, Done

]

where Date>'05-01-2017'

and Status='In progress'

;

Regards

Pratyush

Re: Where statement

Kushal_Chawda — Wed, 27 Sep 2017 09:35:27 GMT

if your Date format is not text then no need to use Date#

T1:

LOAD ID,

Date,

Status

FROM

[\Desktop\ex.xls]

(biff, embedded labels, table is Sheet1$)

where Date > makedate(2017,1,15) and Status='In progress' ;

Re: Where statement

Anonymous — Wed, 27 Sep 2017 09:41:26 GMT

HI to all

Please Focus on this statement

In the table below, only ID=2 should be returned.

Re: Where statement

Kushal_Chawda — Wed, 27 Sep 2017 09:49:10 GMT

do like below

T1:

LOAD ID,

Date ,

if(Date > makedate(2017,1,15) and Status='In progress' ,1,0) as Flag

Status

FROM

[\Desktop\ex.xls]

(biff, embedded labels, table is Sheet1$)

left join(T1)

LOAD distinct ID

1 as Flag2

resident T1

where Flag=1;

Final:

noconcatenate

LOAD *

resident T1

where Flag2=1;

drop table T1;

Re: Where statement

Anonymous — Wed, 27 Sep 2017 09:55:24 GMT

Maybe u can try this,

DATA:

LOAD ID as tes;

LOAD * Inline [

ID,Date,Status

1,01-12-2016,In progress

1,02-01-2017,Waiting

1,10-01-2017,In progress

1,23-01-2017,Done

2,10-01-2017,In progress

2,16-01-2017,Done

]

WHERE Date <= '05-01-2017' and MATCH(Status,'Waiting');

Data2:

LOAD * Inline [

ID,Date,Status

1,01-12-2016,In progress

1,02-01-2017,Waiting

1,10-01-2017,In progress

1,23-01-2017,Done

2,10-01-2017,In progress

2,16-01-2017,Done

]

WHERE Date > '05-01-2017' and MATCH(Status,'In progress')

and not Exists(tes,ID)

Best Regards,

Yislam

---------------------------------------------

When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others.

Re: Where statement

effinty2112 — Wed, 27 Sep 2017 10:21:30 GMT

Hi Nicolai,

Maybe:

Data:

LOAD * INLINE [

ID, Date, Status

1, 01-12-2016, In progress

1, 02-01-2017, Waiting

1, 10-01-2017, In progress

1, 23-01-2017, Done

2, 10-01-2017, In progress

2, 16-01-2017, Done

];

Result:

Load

ID ,Date Where Date > '05-01-2017';

Load

ID,

Min(Date) as Date

Resident Data

Where Status = 'In progress' Group by ID;

DROP Table Data;

giving

ID	Date
2	10-01-2017

Regards

Andrew

Re: Where statement

Kushal_Chawda — Wed, 27 Sep 2017 10:35:38 GMT

this should work

Data:

LOAD ID,

Date,

if(Status='In progress' and Date>MakeDate(2017,1,5),1,

if(Status='In progress' and Date<=MakeDate(2017,1,5),2,0)) as Flag,

Status

FROM

[https://community.qlik.com/thread/276063]

(html, codepage is 1252, embedded labels, table is @1);

Check:

LOAD Distinct ID,

1 as Check1

Resident Data

where Flag=2;

Join(Check)

LOAD Distinct ID,

1 as Check2

Resident Data

where Flag=1;

New:

NoConcatenate

LOAD Distinct ID as ID_New

Resident Check

where len(Check2)>0 and isnull(Check1);

DROP Table Check;

Final:

NoConcatenate

LOAD *

Resident Data

where Exists(ID_New,ID);

DROP Tables Data,New;