MonthStart flag in Load script

[william_fu]

The following script line is giving me an invalid expression error in the load script

If(Date(Date#([DtReferencia], 'YYYYMMDD')) = MonthStart(Max(Date(Date#([DtReferencia], 'YYYYMMDD')))), 1, 0) as FlagCurrentDtRef,

The flag is meant to filter the first day of the most current month in the database.

Can anyone spot the error?

[william_fu]

I guess 'current' was a bad description; I don't actually have any data for the current month (this particular table gets updated with a certain delay) which is why I can't use Today()

[sunny_talwar]

Have you looked at what I proposed? You didn't mention anything about it, hence not sure if you checked and it didn't work or if you forgot to check it out

[william_fu]

Hi Sunny,

Sorry for the delay, I did try it but it doesn't seem to work - I need it to check for the first day of the month of the most recent date.

[sunny_talwar]

When you say recent date, do you mean max date from all your date (not depending on any dimension)?

[william_fu]

Max date from the "DtReferencia" column, regardless of dimension.

For example, right now I only have data for dates until Nov 28th; therefore I want the KPI to filter for Nov 1st.

But whenever the table gets updated do include data until Dec 5th, I want the KPI to filter for Dec 1st.

[sunny_talwar · Accepted solution]

May be this

Table:

LOAD YourFields,

     Date#([DtReferencia], 'YYYYMMDD') as DtReferencia

FROM ....;

Left Join (Table)

LOAD MonthStart(Max(DtReferencia)) as MaxDtReferencia

Resident Table;

FinalTable:

LOAD *,

     If(DtReferencia = MaxDtReferencia, 1, 0) as FlagCurrentDtRef

Resident Table;

DROP Table Table;

[william_fu]

Deu na mesma, acho que o problema não é na formatação da data..

[william_fu]

Thanks Sunny, this is working!

I did notice that the data load took longer as it loaded my Table rows twice (and the Table has a few million records)

Do you have any performance concerns with this method?

[sunny_talwar]

You can use the method here to get the max date... using FieldValue function

Better Calendar Scripts | Qlikview Cookbook