Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

agnaldoneves
Valued Contributor

City with sale and no sales in LOAD

Create filter

City store yes, no

IF NomeMunicipio > VLR_ITEM_FATURAR_TOTAL, 1)  AS Yes

IF NomeMunicipio =NULL(VLR_ITEM_FATURAR_TOTAL),0)  AS NO

I do not know the best way to do this filter?

2017-04-15_09-48-12.png

Store:

LOAD

                

     Num(Replace(VLR_ITEM, '.', ','))           AS     VLR_ITEM_FATURAR_TOTAL,

     CIDADE                                     AS     NomeMunicipio

FROM (qvd)

Where DATA_PEDIDOX >= '2017-01-01';

City:

LOAD ID,

     UF,

     NomeMunicipio,

     NomeMunicipio&' - '&UF as [Mun/UF]

FROM

C:\Geral\qvd\mapa\*_Atributos.qvd

(qvd);

1 Solution

Accepted Solutions
MVP
MVP

Re: City with sale and no sales in LOAD

There are multiple ways to achieve what you want.

For example, create a list box with a field <expression> (from field drop down list on general tab) and enter as expression a calculated dimension like

=Aggr(If(Sum({1} VLR_ITEM_FATURAR_TOTAL),'Yes','No'), NomeMunicipio)

2017-04-15 17_27_25-QlikView x64 - [E__Users_Stefan_Downloads_tmpx1_2.qvw].png

3 Replies
MVP
MVP

Re: City with sale and no sales in LOAD

There are multiple ways to achieve what you want.

For example, create a list box with a field <expression> (from field drop down list on general tab) and enter as expression a calculated dimension like

=Aggr(If(Sum({1} VLR_ITEM_FATURAR_TOTAL),'Yes','No'), NomeMunicipio)

2017-04-15 17_27_25-QlikView x64 - [E__Users_Stefan_Downloads_tmpx1_2.qvw].png

MVP
MVP

Re: City with sale and no sales in LOAD

Or create a new field in your data model,something like

Filter:

LOAD NomeMunicipio, If(Sum(VLR_ITEM_FATURAR_TOTAL)>0,'Yes','No') as Filter

RESIDENT Vendas

GROUP BY NomeMunicipio;

added to your data load script (or use a LEFT JOIN (Estados_Atributos) LOAD prefix to join the filter to your dimensional table, whatever you need or prefer).

agnaldoneves
Valued Contributor

Re: City with sale and no sales in LOAD

thank you