Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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 ?
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
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.
Thanks for that Miguel, it's good to have someone a bit more knowledgeable put things straight!
Chris
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 HurrayBut still my original problem was not solved.. I am not able to compare when TransInDt is null
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.