topic Re: Expression with Dates and Texts criteria in QlikView

Expression with Dates and Texts criteria

rafaeloneil — Sat, 16 Nov 2024 03:40:49 GMT

Guys, good afternoon,

I am trying to create an expression that perform the counting of a field by applying multiple criteria in other fields. The Criteria Are:

GR_MERCADO_2={‘Bradesco’}

UF_LICENCIAMENTO={‘SP’, ‘PE’, ‘SC’}

NOT IsNull(ANO_EMISSAO_DOC)

Date(MakeDate(ANO, MES, DIA), 'DD/MM/YYYY') >= 01/05/2018 **

My Expression is not working:

COUNT({$<GR_MERCADO_2={'Bradesco'}, UF_LICENCIAMENTO={'SP', 'PE', 'SC'}, NOT IsNull(ANO_EMISSAO_DOC), Date(MakeDate(ANO, MES, DIA), 'DD/MM/YYYY') >= 01/05/2018>}[CHASSI/SEQ])

** I need to mount the date because the base does not have a unified date field

Can you give me a direction?

Re: Expression with Dates and Texts criteria

Lisa_P — Mon, 06 May 2019 01:28:49 GMT

Inside the Set Analysis { }, you can define fields, but not the other criteria. Could you find another criteria for the ANO_EMISSAO_DOC field (>0)?

Re: Expression with Dates and Texts criteria

jonathandienst — Mon, 06 May 2019 05:50:14 GMT

The LHS of a set expression clause must be a physical field name, so your requirement cannot be implemented purely in a set expression. You could do this:

COUNT({$< GR_MERCADO_2={'Bradesco'}, UF_LICENCIAMENTO={'SP', 'PE', 'SC'}, ANO_EMISSAO_DOC = {"*"} >} If(MakeDate(ANO, MES, DIA) >= Date#('01/05/2018'), [CHASSI/SEQ]))

Or define a new field with the MakeDate() expression in your load script, and then add the new field criterion to the set expression. Something like this:

DateNew = {">=$(=Date#('01/05/2018'))"}

(the exact syntax depends on your default date format)