Set Analysis with variable and date period greater than less than

[limfungkeat]

Dear Masters out there ! 

I have 2 variables v_DateFrom and v_DateTo, chosen/assigned from Calendar object . Then i need to count total man hrs worked for a list of employee within that date range . The below is not working in my expression . Please assist 

Sum({<[EMP_NM]= {'ABC','XYZ'},APPROVEDDATE = {">=$(=Date(v_DateFrom))<=$(=(v_DateTo))"}>}MANHRS)

Thanks !

Lim

[Shubham_Deshmukh]

Reason why it was not working previously with condition EMP_NM={'A'} because same date has different time, so floor() manipulated that.

[limfungkeat]

thank you so much Shubham ! I got the tips and solution from you man ...

Now , i need to apply this to the data source where i need to run the sql statement and extract the data from Oracle table , instead of using the xls table/ data

[Shubham_Deshmukh]

Great, Kudos !!

[limfungkeat]

Dear Shubham - I tried to apply the same concept by replacing the load statement with the below (am using oracle table as datasource 😞

select EMP_NM as employeename, MANHRS,APPROVEDDATE as appDates

FROM timesheet

the output of the straight  table  is blank .

How to use the Floor function in oracle ? As if I will to re-write the statement like this :

select EMP_NM as employeename, MANHRS, FLOOR(APPROVEDDATE) as appDates

FROM timesheet

I will get error ORA 00932 – Inconsistent data type. Expected number got Date

The data type for APPROVEDDATE in the table is DATE

[Shubham_Deshmukh]

Hi,

You need to use Resident load for the same,

oracleScript:
select EMP_NM as employeename, MANHRS,APPROVEDDATE as appDates FROM timesheet ;
NoConcatenate
Load EMP_NM as employeename, MANHRS, floor(APPROVEDDATE) as appDates
Resident oracleScript;
Drop table oracleScript ;

[limfungkeat]

the revised script as below :

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

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

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

SET DateFormat='YYYY-MM-DD';

SET TimestampFormat='YYYY-MM-DD 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';

ODBC CONNECT32 TO [eForm Prod;DBQ=PHARMADB ] (XUserId is aABGUCZIPSUaEND, XPassword is EWUBEYFMTbcOXXJOELSA);

oracleScript:

select EMP_NM as employeename, MANHRS,APPROVEDDATE as appDates FROM timesheet_npd ;

NoConcatenate

Load EMP_NM as employeename, MANHRS, floor(APPROVEDDATE) as appDates

Resident oracleScript;

Drop table oracleScript ;

/*

LOAD EMP_NM,

MANHRS,

//APPROVEDDATE as appDates

Floor(APPROVEDDATE) as appDates

FROM

[C:\Backup\QlikView\My QlikviewBR\timesheet.xls]

(biff, embedded labels, table is [Sheet 1$]);

*/

and I am getting the error while reloading the script as :

[Shubham_Deshmukh]

Ohh Sorry...due to EMP_NM as employeename, it won't recognize EMP_NM in resident load.
Load only EMP_NM only (without alias name) or use employeename in resident load.
Apply same thing to other fields also.

-Shubham

[limfungkeat]

Changed to the below :

oracleScript:

select EMP_NM as employeename, MANHRS,APPROVEDDATE as appDates FROM timesheet_npd ;

NoConcatenate

Load EMP_NM,MANHRS,floor(APPROVEDDATE) as appDates

Resident oracleScript;

Drop table oracleScript ;

and still getting this :

[Shubham_Deshmukh]

I meant it like below, 

oracleScript:
select EMP_NM, MANHRS,APPROVEDDATE FROM timesheet ;
NoConcatenate
Load EMP_NM as emplyeenames, MANHRS, floor(APPROVEDDATE) as appDates
Resident oracleScript;
Drop table oracleScript ;

[limfungkeat]

Fantastic Shubham ! Thanks a lot man