Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
nicolai_moller
Contributor
Contributor

Where statement

Hi

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.

IDDateStatus
101-12-2016In progress
102-01-2017Waiting
110-01-2017In progress
123-01-2017Done
210-01-2017In progress
216-01-2017Done

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

1 Solution

Accepted Solutions
Kushal_Chawda

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;

View solution in original post

9 Replies
Anonymous
Not applicable

!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
210-01-2017In progress

Regards

Tahemas

Kushal_Chawda

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

prat1507
Specialist
Specialist

Hi Nicolai

This would be returned

110-01-2017In progress
210-01-2017In 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

Kushal_Chawda

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

Anonymous
Not applicable

HI to all

Please Focus on this statement

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

Kushal_Chawda

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;




Anonymous
Not applicable

Maybe u can try this,

hasil.PNG

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.

effinty2112
Master
Master

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
210-01-2017

Regards

Andrew

Kushal_Chawda

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;