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?

[sunny_talwar]

Do you really need Max? May be this:

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

[felipedl]

You're using an aggregation Max inside a conditional statement.

If you only use the MonthStart(Max(Date(Date#([DtReferencia], 'YYYYMMDD')))) statement and a group by clause, it works fine.

For example:

data:

load * Inline

[

Cliente, Data Transação, DtReferencia,Ano Transação, Transação

1, 01/01/2017,01/01/2017,2017, 1

];

// This load statement works

tmp:

Load

Cliente,

MonthStart(Max(Date(Date#([DtReferencia], 'YYYYMMDD')))) as FlagCurrentDtRef

Resident data

Group by Cliente;

// This one does not:

tmp:

Load

Cliente,

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

Resident data

Group by Cliente;

Felipe.

[agigliotti]

you can do it in two steps as below:

tab1:

LOAD

     MonthStart(Max(Date(Date#([DtReferencia], 'YYYYMMDD')))) as MaxDate

FROM .....;

let vL.MaxDate = FieldValue(MaxDate, 1);

tab2:

LOAD

     if( Date(Date#([DtReferencia], 'YYYYMMDD')) = '$(vL.MaxDate)', 1, 0 ) as FlagCurrentDtRef

FROM .....;

DROP Table tab1;

[william_fu]

Hi Andrea,

I'm very new to scripting, does it matter which section I put the script in?

Here's the error I got.

[william_fu]

Hey Felipe,

How can I use this flag within Set Analysis?

Obrigado!

[agigliotti]

check the value of variable vL.MaxDate because it seems to be empty.

[shraddha_g]

Share sample app with sample data if possible and expected output

[felipedl]

Hi William,

Lets say you have the code below:

dummy:

load * Inline

[

Cliente, Data Transação, DtReferencia,Ano Transação, Transação

1, 01/01/2017,01/01/2017,2017, 1

3, 04/12/2017,04/12/2017,2017, 2

2, 01/12/2017,01/12/2017,2017, 5

];

data:

Load

Cliente,

[Data Transação],

DtReferencia,

[Ano Transação],

Transação,

if ([Data Transação] = MonthStart(Today()),1,0) as [Flag Current Date MonthStart]

Resident dummy;

drop table dummy;

Use the expression:

sum({<[Flag Current Date MonthStart]={1}>}Transação)

To get the sum of all [Transação] fields where the flag is set, getting uyou:

You'll get the sum only when the flag is set where the [Data Transação] is equal to the current month's start date (01/12/2017).

[Anonymous]

Tenta isso aqui cara

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