topic Load with condition in QlikView

Load with condition

Anonymous — Fri, 17 May 2013 14:17:24 GMT

Hello,

Please help me load all data that more 100 for each studyID.

Please see my script.

LET dDateday = day(date(MonthStart(today(),-4)));

LET dDatemonth = num(month(date(MonthStart(today(),-4))));

LET dDateyear = year(date(MonthStart(today(),-4)));

LET dDate = date(MonthStart(today(),-4));

LET d1Dateday = day(date(MonthStart(today())));

LET d1Datemonth = num(month(date(MonthStart(today()))));

LET d1Dateyear = year(date(MonthStart(today())));

Generic

LOAD respondentID, comeTime, studyID, MonthName(Monthstart(comeTime)) as MYear,

'question' &questionID,

tagID;

SQL SELECT *

FROM "Source".dbo.Qlikview

WHERE sectorID=23 and

(CAST(( STR( ( YEAR(comeTime) ) ) + '/' + STR( MONTH(comeTime) ) + '/' + STR( DAY(comeTime) ) ) AS DATETIME) ) >= CAST(( STR( ( $(dDateyear) ) ) +

'/' + STR( ($(dDatemonth)) ) + '/' + STR(($(dDateday)) ) ) AS DATETIME)

and

(CAST(( STR( ( YEAR(comeTime) ) ) + '/' + STR( MONTH(comeTime) ) + '/' + STR( DAY(comeTime) ) ) AS DATETIME) ) < CAST(( STR( ( $(d1Dateyear) ) ) +

'/' + STR( ($(d1Datemonth)) ) + '/' + STR(($(d1Dateday)) ) )

AS DATETIME) ;

Thanks,

Re: Load with condition

Anonymous — Fri, 17 May 2013 15:00:28 GMT

If just load with condition studyID>100

LOAD respondentID, comeTime, studyID, MonthName(Monthstart(comeTime)) as MYear,

questionID,

tagID;

SQL SELECT *

FROM "Source".dbo.Qlikview

WHERE sectorID=23

If I add

FROM "Source".dbo.Qlikview

WHERE sectorID=23 and count(studyID)>100

doesn't work

Re: Load with condition

Fri, 17 May 2013 15:31:58 GMT

sure it doesn't

You must do

LOAD respondentID, studyID, comeTime, MonthName(Monthstart(comeTime)) as MYear,

questionID,

tagID;

SQL SELECT

respondentID, comeTime, MonthName(Monthstart(comeTime)) as MYear,

questionID,

tagID,

count(StudyID)

FROM "Source".dbo.Qlikview

WHERE sectorID=23

group by

respondentID, comeTime, MonthName(Monthstart(comeTime)) as MYear,

questionID,

tagID;

having count(studyID)>100

Re: Load with condition

Anonymous — Fri, 17 May 2013 18:15:47 GMT

It should works, but no.

Maybe because of data

Re: Load with condition

Anonymous — Tue, 21 May 2013 18:58:01 GMT

Tab1:

load studyID;

select studyID

from "Source".dbo.Qlikview

where sectorID=23

and (CAST(( STR( ( YEAR(comeTime) ) ) + '/' + STR( MONTH(comeTime) ) + '/' + STR( DAY(comeTime) ) ) AS DATETIME) ) >= CAST(( STR( ( $(dDateyear) ) ) +

'/' + STR( ($(dDatemonth)) ) + '/' + STR(($(dDateday)) ) ) AS DATETIME)

and

(CAST(( STR( ( YEAR(comeTime) ) ) + '/' + STR( MONTH(comeTime) ) + '/' + STR( DAY(comeTime) ) ) AS DATETIME) ) < CAST(( STR( ( $(d1Dateyear) ) ) +

'/' + STR( ($(d1Datemonth)) ) + '/' + STR(($(d1Dateday)) ) )

AS DATETIME)

group by studyID

having COUNT(respondentId) >=100;

Inner Join (Tab1)

Tab2:

LOAD respondentID, comeTime, studyID, MonthName(Monthstart(comeTime)) as MYear,

questionID,

tagID;

SELECT respondentID, comeTime, studyID, comeTime,

questionID,

tagID

from "Source".dbo.Qlikview

where sectorID=23

and (CAST(( STR( ( YEAR(comeTime) ) ) + '/' + STR( MONTH(comeTime) ) + '/' + STR( DAY(comeTime) ) ) AS DATETIME) ) >= CAST(( STR( ( $(dDateyear) ) ) +

'/' + STR( ($(dDatemonth)) ) + '/' + STR(($(dDateday)) ) ) AS DATETIME)

and

(CAST(( STR( ( YEAR(comeTime) ) ) + '/' + STR( MONTH(comeTime) ) + '/' + STR( DAY(comeTime) ) ) AS DATETIME) ) < CAST(( STR( ( $(d1Dateyear) ) ) +

'/' + STR( ($(d1Datemonth)) ) + '/' + STR(($(d1Dateday)) ) )

AS DATETIME);

Industry:

Generic

LOAD respondentID, studyID, comeTime, MonthName(Monthstart(comeTime)) as MYear,

'question' &questionID,

tagID

Resident Tab1;

DROP Table Tab1;