Load with condition

[Anonymous]

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,

[Anonymous]

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

[#3]

Hi

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

[Anonymous]

It should works, but no.

Maybe because of data

[Anonymous · Accepted solution]

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;