Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
tom2qlik
Contributor

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

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

Tags (3)
1 Solution

Accepted Solutions

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

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;

4 Replies

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

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

tom2qlik
Contributor

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

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

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

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;