Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone......
Does anyone see anything with my logic in this large if statement? I am getting nothing out of it.....Thanks
If(Len(trim([Date type_DAR01]) > 0),if([Date type_DAR01] = '11',Date_DAT01) or
If(Len(trim([Date type_DAR02]) > 0),if([Date type_DAR02] = '11',Date_DAT02) or
If(Len(trim([Date type_DAR03]) > 0),if([Date type_DAR03] = '11',Date_DAT03) or
If(Len(trim([Date type_DAR04]) > 0),if([Date type_DAR04] = '11',Date_DAT04) or
If(Len(trim([Date type_DAR05]) > 0),if([Date type_DAR05] = '11',Date_DAT05) or
If(Len(trim([Date type_DAR06]) > 0),if([Date type_DAR06] = '11',Date_DAT06) or
If(Len(trim([Date type_DAR07]) > 0),if([Date type_DAR07] = '11',Date_DAT07) or
If(Len(trim([Date type_DAR08]) > 0),if([Date type_DAR08] = '11',Date_DAT08) or
If(Len(trim([Date type_DAR09]) > 0),if([Date type_DAR09] = '11',Date_DAT09) or
If(Len(trim([Date type_DAR10]) > 0),if([Date type_DAR10] = '11',Date_DAT10) or
If(Len(trim([Date type_DAR11]) > 0),if([Date type_DAR11] = '11',Date_DAT11) or
If(Len(trim([Date type_DAR12]) > 0),if([Date type_DAR12] = '11',Date_DAT12))))))))))))) as Hire_Date,
If(Len(trim([Date type_DAR02]) > 0),if([Date type_DAR02] = '42',Date_DAT02) or
If(Len(trim([Date type_DAR03]) > 0),if([Date type_DAR03] = '42',Date_DAT03) or
If(Len(trim([Date type_DAR04]) > 0),if([Date type_DAR04] = '42',Date_DAT04) or
If(Len(trim([Date type_DAR05]) > 0),if([Date type_DAR05] = '42',Date_DAT05) or
If(Len(trim([Date type_DAR06]) > 0),if([Date type_DAR06] = '42',Date_DAT06) or
If(Len(trim([Date type_DAR07]) > 0),if([Date type_DAR07] = '42',Date_DAT07) or
If(Len(trim([Date type_DAR08]) > 0),if([Date type_DAR08] = '42',Date_DAT08) or
If(Len(trim([Date type_DAR09]) > 0),if([Date type_DAR09] = '42',Date_DAT09) or
If(Len(trim([Date type_DAR10]) > 0),if([Date type_DAR10] = '42',Date_DAT10) or
If(Len(trim([Date type_DAR11]) > 0),if([Date type_DAR11] = '42',Date_DAT11) or
If(Len(trim([Date type_DAR12]) > 0),if([Date type_DAR12] = '42',Date_DAT12)))))))))))) as Term_Date,
You just want one record for one Emp_ID? May be you just want to group by just Emp_ID
Tempx:
LOAD Emp_ID,
Date(Only(Emp_EndDate)) as Emp_EndDate,
Date(Only(Hire_Date)) as Hire_Date,
Date(Only(Term_Date)) as Term_Date,
Sum(EmpCnt) as NoHits
Resident Temp
Group By Emp_ID;
DROP Table Temp;
UPDATE: I am assuming that each Emp_ID has only one Emp_EndDate, Hire_Date, Term_Date
May be this (Remove the or and replace it with commas)
If(Len(trim([Date type_DAR01]) > 0),
if([Date type_DAR01] = '11',Date_DAT01),
If(Len(trim([Date type_DAR02]) > 0),
if([Date type_DAR02] = '11',Date_DAT02),
...
Thanks Sunny.....That worked great. However I try and take the output into this:
Temp:
Load [-_ENDDA] as Emp_EndDate,
PersNo._PERNR as Emp_ID,
If(Len(trim([Date type_DAR01]) > 0),if([Date type_DAR01] = '11',Date_DAT01),
If(Len(trim([Date type_DAR02]) > 0),if([Date type_DAR02] = '11',Date_DAT02),
If(Len(trim([Date type_DAR03]) > 0),if([Date type_DAR03] = '11',Date_DAT03),
If(Len(trim([Date type_DAR04]) > 0),if([Date type_DAR04] = '11',Date_DAT04),
If(Len(trim([Date type_DAR05]) > 0),if([Date type_DAR05] = '11',Date_DAT05),
If(Len(trim([Date type_DAR06]) > 0),if([Date type_DAR06] = '11',Date_DAT06),
If(Len(trim([Date type_DAR07]) > 0),if([Date type_DAR07] = '11',Date_DAT07),
If(Len(trim([Date type_DAR08]) > 0),if([Date type_DAR08] = '11',Date_DAT08),
If(Len(trim([Date type_DAR09]) > 0),if([Date type_DAR09] = '11',Date_DAT09),
If(Len(trim([Date type_DAR10]) > 0),if([Date type_DAR10] = '11',Date_DAT10),
If(Len(trim([Date type_DAR11]) > 0),if([Date type_DAR11] = '11',Date_DAT11),
If(Len(trim([Date type_DAR12]) > 0),if([Date type_DAR12] = '11',Date_DAT12))))))))))))) as Hire_Date,
If(Len(trim([Date type_DAR02]) > 0),if([Date type_DAR02] = '42',Date_DAT02),
If(Len(trim([Date type_DAR03]) > 0),if([Date type_DAR03] = '42',Date_DAT03),
If(Len(trim([Date type_DAR04]) > 0),if([Date type_DAR04] = '42',Date_DAT04),
If(Len(trim([Date type_DAR05]) > 0),if([Date type_DAR05] = '42',Date_DAT05),
If(Len(trim([Date type_DAR06]) > 0),if([Date type_DAR06] = '42',Date_DAT06),
If(Len(trim([Date type_DAR07]) > 0),if([Date type_DAR07] = '42',Date_DAT07),
If(Len(trim([Date type_DAR08]) > 0),if([Date type_DAR08] = '42',Date_DAT08),
If(Len(trim([Date type_DAR09]) > 0),if([Date type_DAR09] = '42',Date_DAT09),
If(Len(trim([Date type_DAR10]) > 0),if([Date type_DAR10] = '42',Date_DAT10),
If(Len(trim([Date type_DAR11]) > 0),if([Date type_DAR11] = '42',Date_DAT11),
If(Len(trim([Date type_DAR12]) > 0),if([Date type_DAR12] = '42',Date_DAT12)))))))))))) as Term_Date,
1 as EmpCnt
Resident PA0041
;
drop Table PA0041;
Tempx:
Load Emp_ID,
Emp_EndDate,
Hire_Date,
Term_Date,
Sum(EmpCnt) as NoHits
Resident Temp
group by Emp_ID, Emp_EndDate, Hire_Date, Term_Date
;
drop Table Temp;
I still get multiple records for each employee.....any ideas.....
You just want one record for one Emp_ID? May be you just want to group by just Emp_ID
Tempx:
LOAD Emp_ID,
Date(Only(Emp_EndDate)) as Emp_EndDate,
Date(Only(Hire_Date)) as Hire_Date,
Date(Only(Term_Date)) as Term_Date,
Sum(EmpCnt) as NoHits
Resident Temp
Group By Emp_ID;
DROP Table Temp;
UPDATE: I am assuming that each Emp_ID has only one Emp_EndDate, Hire_Date, Term_Date
It looked that you used a crosstable and it could be worthful to consider to transform it into a normal data-structure, see also: The Crosstable Load.
- Marcus
Perfect.....thanks again for all your help. I tried grouping on just Emp_ID, but I did not format the dates the way you did. Maybe that makes a difference.
Thom
Yes. An employee can have only one hire date and end date.