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: 
william_fu
Creator II
Creator II

MonthStart flag in Load script

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?

1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

18 Replies
sunny_talwar

Do you really need Max? May be this:

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

felipedl
Partner - Specialist III
Partner - Specialist III

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
Partner - Champion
Partner - Champion

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
Creator II
Creator II
Author

Hi Andrea,

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

Here's the error I got.

scripterror.PNG

william_fu
Creator II
Creator II
Author

Hey Felipe,

How can I use this flag within Set Analysis?

Obrigado!

agigliotti
Partner - Champion
Partner - Champion

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

shraddha_g
Partner - Master III
Partner - Master III

Share sample app with sample data if possible and expected output

felipedl
Partner - Specialist III
Partner - Specialist III

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:

Sample.png

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
Not applicable

Tenta isso aqui cara


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