topic Re: If Logic with (Len(Trim) in QlikView

If Logic with (Len(Trim)

tmumaw — Wed, 20 Apr 2016 13:25:20 GMT

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,

Re: If Logic with (Len(Trim)

sunny_talwar — Wed, 20 Apr 2016 13:28:07 GMT

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

Re: If Logic with (Len(Trim)

tmumaw — Wed, 20 Apr 2016 13:40:06 GMT

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

Re: If Logic with (Len(Trim)

sunny_talwar — Wed, 20 Apr 2016 13:46:01 GMT

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

Re: If Logic with (Len(Trim)

marcus_sommer — Wed, 20 Apr 2016 13:48:33 GMT

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

Re: If Logic with (Len(Trim)

tmumaw — Wed, 20 Apr 2016 13:49:18 GMT

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

Re: If Logic with (Len(Trim)

tmumaw — Wed, 20 Apr 2016 13:55:47 GMT

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