Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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()
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
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.
When you say recent date, do you mean max date from all your date (not depending on any dimension)?
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.
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;
Deu na mesma, acho que o problema não é na formatação da data..
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?
You can use the method here to get the max date... using FieldValue function