Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

williamfuu
Contributor 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?

Tags (1)
1 Solution

Accepted Solutions

Re: MonthStart flag in Load script

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;

18 Replies

Re: MonthStart flag in Load script

Do you really need Max? May be this:

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

felipedl
Valued Contributor III

Re: MonthStart flag in Load script

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
Honored Contributor II

Re: MonthStart flag in Load script

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;

williamfuu
Contributor II

Re: MonthStart flag in Load script

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

williamfuu
Contributor II

Re: MonthStart flag in Load script

Hey Felipe,

How can I use this flag within Set Analysis?

Obrigado!

agigliotti
Honored Contributor II

Re: MonthStart flag in Load script

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

shraddha_g
Honored Contributor III

Re: MonthStart flag in Load script

Share sample app with sample data if possible and expected output

felipedl
Valued Contributor III

Re: MonthStart flag in Load script

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).

psilva001
Contributor

Re: MonthStart flag in Load script

Tenta isso aqui cara


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



Community Browser