Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I'm trying to write an if statement which goes something like this:
If(Min(Date) and Flag = 'DE-ACTIVE', ApplyMap('SubmitDate', RefNo)) as Date
I know this isn't correct but I'm not sure how to fix it to make it correct.
I'm looking for the minimum date and want to see if the flag on that date is 'De-Active'. If it is then do the apply map.
Thanks,
Tom
Per each Product?? May be like this
Fact:
LOAD Product,
Date,
Flag,
RefNo,
ProductFlag,
....
FROM ....;
Left Join (Fact)
LOAD Product,
Min(Date) as MinDate
Resident Fact
Group By Product;
FinalFact:
LOAD *,
If(MinDate = Date and Flag = 'DE-ACTIVE', ApplyMap('SubmitDate', RefNo)) as Date
Resident Fact;
DROP Table Fact;
Is this overall min date or is this min date per dimension or a list of dimensions?
The min date is per dimension.
Load
RefNo,
If(Min(Date) and Flag = 'DE-ACTIVE', ApplyMap('SubmitDate', RefNo)) as Date ,
ProductFlag
Resident Active;
Per each Product?? May be like this
Fact:
LOAD Product,
Date,
Flag,
RefNo,
ProductFlag,
....
FROM ....;
Left Join (Fact)
LOAD Product,
Min(Date) as MinDate
Resident Fact
Group By Product;
FinalFact:
LOAD *,
If(MinDate = Date and Flag = 'DE-ACTIVE', ApplyMap('SubmitDate', RefNo)) as Date
Resident Fact;
DROP Table Fact;
should be like below
left join(Active)
Load
RefNo,
Min(Date) as Date
ProductFlag
1 as MinDateFlag
Resident Active
group by RefNo,ProductFlag ;
New:
LOAD *,
If(MinDateFlag=1 and ProductFlag = 'DE-ACTIVE', ApplyMap('SubmitDate', RefNo)) as Date
resident Active;
drop table Active;