If Logic with (Len(Trim)

[tmumaw]

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,

[sunny_talwar]

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),
...

[tmumaw]

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.....

[sunny_talwar · Accepted solution]

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

[marcus_sommer]

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

[tmumaw]

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

[tmumaw]

Yes.  An employee can have only one hire date and end date.