Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Friends,
My requirement is
1. I have Two fields BoxNumber AND CommissionNumber
one Commission Number Has Multiple BoxNumber
First i need to Dervie the count of BoxNumberFrom the Commission Numer.
How do i do this?
2. After Deriving the BoxNumber for the particular Commission Number that needs to be used it in the expression
if((isnull(unloadingDate)=0 and Box_Flag='Y')and isnull(Lineissuedate)= -1 and subrea <> 'Line' and Location = 'STOREHOUE' )
Then i need to get the count of BoxNumber which is derived from the commission Number i Have mentioned....so how can i formulate the above conditions in
set analysis or exact if expression?
I am using all the Expressions in Pivot
Thanks in Advance
Nikitha Jain
Hi,
1.U can use aggr function to get the count of box numbers like aggr(count(box_no),commission_no)
for second one, i need some more clarifications.
try to upload some sample data and explain the if condition where u want to use this count of box numbers.
Hi Nikitha,
First of all create a flag which will tell you whether unloading date and Lineissuedate is null or not.
Something like shown below.
Load *,
If(Isnull(Lineissuedate),1,0) as Lineissuedate_Isnull_Flag,
If(Isnull(LoadingDate),1,0) as LoadingDate_Isnull_Flag
From xyz.
Then use the below expression to find the count of Boxnumber with the said conditions.
Sum(Aggr(Count({<subrea -= {'Line'},Location = {'STOREHOUE'},Lineissuedate_Isnull_Flag = {"0"},LoadingDate_Isnull_Flag = {"0"}, Box_Flag={'Y'}>}BoxNumber),CommissionNumber))
Regards,
Kaushik Solanki