City with sale and no sales in LOAD

[Ribeiro]

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?

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

[swuehl · Accepted solution]

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)

[swuehl]

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

[Ribeiro]

thank you