topic Finding the flag value of a min(date) in script in QlikView

Finding the flag value of a min(date) in script

tom2qlik — Thu, 14 Sep 2017 08:29:17 GMT

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

Re: Finding the flag value of a min(date) in script

sunny_talwar — Thu, 14 Sep 2017 08:32:51 GMT

Is this overall min date or is this min date per dimension or a list of dimensions?

Re: Finding the flag value of a min(date) in script

tom2qlik — Thu, 14 Sep 2017 08:36:43 GMT

The min date is per dimension.

Load
RefNo,
If(Min(Date) and Flag = 'DE-ACTIVE', ApplyMap('SubmitDate', RefNo)) as Date ,
ProductFlag
Resident Active;

Re: Finding the flag value of a min(date) in script

sunny_talwar — Thu, 14 Sep 2017 08:40:43 GMT

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;

Re: Finding the flag value of a min(date) in script

Kushal_Chawda — Thu, 14 Sep 2017 08:44:03 GMT

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;