Not able to comparing date in set analysis

[#1]

Hi,

I have written an expression in set analysis to filter some records.

The given below is the expression i am using :

I am trying to eliminate the records with Action reason in BTF,XTF,ICT,TAF,XFL,XFS and I need to eliminate the records where Transfer In date <Transfer Out date . This is working fine and behaving wierd for Nulls. I am not able to compare null values. I am getting reocrds if transfer in date is null, which i dont need. Is there any work around.

The given below is the query i am using

=Count(

{

$<

[Hire YearMonth] = {"<=$(=MAX(FullMonth))"},

[HADYearMonth] = {"<=$(=Year(MAX(Date + 3)) * 10000 + Month(MAX(Date + 3)) * 100 + Day(MAX(Date + 3)))"}

>

*

(

$<TerminationDateAsNull = {"1"}> +

$<

[TADYearMonth]= {">$(=Year(MAX(Date + 3)) * 10000 + Month(MAX(Date + 3)) * 100 + Day(MAX(Date + 3)))"},

[TerminationYearMonth]={"<=$(=MAX(FullMonth))"}

>

+

$<

[TerminationYearMonth]={">$(=MAX(FullMonth))"}

>

)

- 1 < [TransOutActReason] = {BTF,XTF,ICT,TAF,XFL,XFS}

, [TransInForNullYear]={"<(=TransOutYearMonth)"} --> This is not working >

}

distinct EmplId

Please suggest

Thanks,

Srihari

[chris_johnson]

Hi,

I suppose the easiest way would be to remove the date comparison out of the set analysis and use it as an IF statement. Possibly something like:

=count({<set analysis here>} distinct if(TransInForNullYear<TransOutYearMonth, EmplId, null()))

That's just off the top of my head though, it may need a bit of fiddling with. Probably more likely to get you a result quicker than trying to figure out how to do it with set analysis in my experience.

Regards,

Chris

[#3]

Hi Chris,

Thanks for the reply. I have tried your answer. Its not working for me.

I need to filter the records where TransferOutActionReason in BTF,XTF,ICT,TAF,XFL,XFS , only if transfer in date is less than the transfer out date.

The given below is the query i am using..but not working

=Count(

{

$<

[Hire YearMonth] = {"<=$(=MAX(FullMonth))"},

[HADYearMonth] = {"<=$(=Year(MAX(Date + 3)) * 10000 + Month(MAX(Date + 3)) * 100 + Day(MAX(Date + 3)))"}

>

*

(

$<TerminationDateAsNull = {"1"}> +

$<

[TADYearMonth]= {">$(=Year(MAX(Date + 3)) * 10000 + Month(MAX(Date + 3)) * 100 + Day(MAX(Date + 3)))"},

[TerminationYearMonth]={"<=$(=MAX(FullMonth))"}

>

+

$<

[TerminationYearMonth]={">$(=MAX(FullMonth))"}

>

)

}

distinct if( [TransInForNullYear]<TransOutYearMonth and [TransOutActReason] =BTF,XTF,ICT,TAF,XFL,XFS ,EmplId,null())

)

Thanks,

srihari

[chris_johnson]

Try using the match() function also (I just found it myself!):

distinct if(([TransInForNullYear<TransOutYearMonth) and (match(TransOutActReason,'BTF','XTF','ICT','TAF','XFL','XFS'), EmplID,null()))

Chris

[#5]

Hi Chris,

I tried this and even pick is not working..

Can we use the same in the load script and put a flag against it and use in set analysis.

Can we use a if statement between date and String :

Like If( (TransInDt<TransOutDt)and (TransOutActReason IN (BTF,XTF,ICT,TAF,XFL,XFS)) ,1,0) as Hurray ?

[chris_johnson]

I would think so, but I'm not sure how well 'IN' works. Maybe try that and if it doesn't work try using match() instead

Chris

[Miguel_Angel_Baeyens]

Hello Chris,

As you mention, IN doesn't exist in QlikView, and Match() is a good alternative:

MATCH(TransOutActReason, 'BTF', 'XTF', 'ICT', 'TAF', 'XFL', 'XFS')

Will return zero if the value in "TransOutActReason" doesn't match with any of the following, and greater than zero otherwise. Values must be quoted if they are literals (strings).

Regards.

[chris_johnson]

Thanks for that Miguel, it's good to have someone a bit more knowledgeable put things straight!

Chris

[#9]

Hi,

i have used the below expression . But still the

If

((TransInDt<TransOutDt) and Match((TransOutActReason),'BTF','XTF','ICT','TAF','XFL','XFS'),1,0) as Hurray

But still my original problem was not solved.. I am not able to compare when TransInDt is null

[Miguel_Angel_Baeyens]

Although there is a IsNull() function, I always prefer to use "Len(field) > 0" to control for an empty or null field, so you can add this to your expression above, something like

If((If(Len(TransInDt) > 0, TransInDt, AnotherFieldDateHere) < TransOutDt) AND Match((TransOutActReason),'BTF','XTF','ICT','TAF','XFL','XFS'),1,0)

Hope that helps.