Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I evreyone
I have a script below which creates one table.
Staff:
Load
BIUNIQUEID,
FIRSTNAME AS "First Name",
GUID,
JOBGRADE AS "Job Grade",
LASTNAME AS "Surname",
NABFULLNAME AS "Lotus Notes Name",
NABCOMMONNAME AS "Full Name",
"PWC_SERVICE_LINE" AS LoS,
"PWC_SERVLINE_SPLTY" AS "Department",
Floor ("RE_DAY") as "Termination Date",
Year("RE_DAY") AS Year,
Month("RE_DAY") as Month;
SQL SELECT *
FROM EMPLOYEES",
//DATES CALCULATIONS
Left Join
Load
BIUNIQUEID,
if("Termination Date"<=today(),1,0) as Terminated,
InMonth("Termination Date", today(), 0)as Terminate_ThisMonth,
InMonth("Termination Date", today(), 1) as Terminate_NextMonth,
InMonth("Termination Date", today(), 2) as Terminate_InTwoMonths,
InMonth("Termination Date", today(), 3) as Terminate_InThreeMonths
Resident STAFF;
I now want to use a CrossTable funtion to combine "Date Calculation" fields into one field so that I can use it in a listbox. I am struglling to get it right.
Please help
Load
[Termination Date],
If([Termination Date] <= Today(), 'Terminated',
If([Termination Date] <= AddMonths(Today(),1), 'Current Month',
If([Termination Date] <= AddMonths(Today(),2), 'Next Month',
If([Termination Date] <= AddMonths(Today(),3), 'In 2 Months',
If([Termination Date] <= AddMonths(Today(),4), 'In 3 Months','More than 3 Months'))))) as Flag
Inline
[
Termination Date
01/10/2014
15/11/2014
25/12/2014
20/01/2015
25/02/2015
];
By changing 1,2,3,4 you can change the definition of Next Month, In 2 Months, In 3 Months etc...
Flag is the List Box Field you are requiring...
Load
[Termination Date],
If([Termination Date] <= Today(), 'Terminated',
If([Termination Date] <= AddMonths(Today(),1), 'Current Month',
If([Termination Date] <= AddMonths(Today(),2), 'Next Month',
If([Termination Date] <= AddMonths(Today(),3), 'In 2 Months',
If([Termination Date] <= AddMonths(Today(),4), 'In 3 Months','More than 3 Months'))))) as Flag
Inline
[
Termination Date
01/10/2014
15/11/2014
25/12/2014
20/01/2015
25/02/2015
];
By changing 1,2,3,4 you can change the definition of Next Month, In 2 Months, In 3 Months etc...
Flag is the List Box Field you are requiring...
Or
Load
[Termination Date],
IF([Termination Date] <= Today(), 'Terminated',
IF(InMonth([Termination Date],Today(),0),'This Month',
IF(InMonth([Termination Date],Today(),1),'Next Month',
IF(InMonth([Termination Date],Today(),2),'In Two Month',
IF(InMonth([Termination Date],Today(),3),'In Three Month','More Than 3 Months'))))) as Flag2
Inline
[
Termination Date
01/10/2014
15/11/2014
25/12/2014
20/01/2015
10/12/2014
25/02/2015
18/03/2015
25/05/2015
01/01/2015
01/12/2014
01/11/2014
];
Flag2 is the field you need to use in List Box
if you strucked here then you need to do one time work here (creting one time QVD)
first create QVD of the file and load that qvd do cross table /transform whatever
after getting cross table script now replace 'from qvd path' in crosstable with "resident staff"
crosstble(....)
Load .
.
.
.
from qvd;// remove this line and replace with resident
Thanks so much
Works much better than what I wanted to do
Thanks for your assistance. You have saved me a lot of time indeed.