Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
tom2qlik
Creator
Creator

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

1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

4 Replies
sunny_talwar

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

tom2qlik
Creator
Creator
Author

The min date is per dimension.

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

sunny_talwar

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;

Kushal_Chawda

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;