Where statement

[nicolai_moller]

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.

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

[Anonymous]

!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

[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]

Hi Nicolai

This would be returned

1

10-01-2017

In progress

2

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

[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]

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]

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.

[effinty2112]

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

[Kushal_Chawda · Accepted solution]

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;