topic Re: Calculating with case statement problems in QlikView

Calculating with case statement problems

Fri, 07 Mar 2014 11:28:38 GMT

Hi all!

I am trying to use something similar a case statement function, and it works, but not always. I explain myself. This function works when all dimensions are expanded, but if any of that dimensions are not expanded, the result are not working properly. I have been several hours trying but i can't understand why this happens. The obvious is that, when there is a dimension which is not expanded, it uses the acumulated values, instead of each [Clave Expediente] values (this is they key of the table). If I expand everything, it calculates for each [Clave Expediente] and works as it should. Any help with that? Thanks!

The code is:

count(DISTINCT [Clave Expediente])

*

if(match([Tipo de Encargo],'TAS01'), pond_TAS01,

if(match([Tipo de Encargo],'TAS02'), pond_TAS02,

if(match([Tipo de Encargo],'TAS03'), pond_TAS03,

if(match([Tipo de Encargo],'EST01'), pond_EST01,

if(match([Tipo de Encargo],'EST02'), pond_EST02,

if(match([Tipo de Encargo],'INF01'), pond_INF01,

if(match([Tipo de Encargo],'INF02'), pond_INF02,

if(match([Tipo de Encargo],'INF03'), pond_INF03,

if(match([Tipo de Encargo],'MOD05'), pond_MOD05,

1)))))))))

*

if(match([Tipo de bien],'APARCAMIENTOS Y TRASTEROS'), pond_aparcamientos,

if(match([Tipo de bien],'RESIDENCIAL'), pond_residencial,

if(match([Tipo de bien],'COMERCIAL'), pond_comercial,

if(match([Tipo de bien],'INDUSTRIAL'), pond_industrial,

if(match([Tipo de bien],'OFICINAS'), pond_oficinas,

if(match([Tipo de bien],'EXPLOTACIONES "ALOJAMIENTO"'), pond_explotaciones,

if(match([Tipo de bien],'EXPLOTACIONES "SECTOR AGRARIO"'), pond_explotacionsrustica,

if(match([Tipo de bien],'FINCAS RUSTICAS'), pond_rusticas,

if(match([Tipo de bien],'OTRAS EXPLOTACIONES'), pond_explotaciones,

if(match([Tipo de bien],'SOLARES Y TERRENO'), pond_suelos,

if(match([Tipo de bien],'BIENES MUEBLES'), pond_bienesmuebles,

1)))))))))))

*

if(match([Rol],'Tecnico/a'), pond_TEC,

if(match([Rol],'Supervisor/a'), pond_SUP,

if(match([Rol],'Supervisor/a Superior'), pond_SUPSUP,

1)))

*

if([Valor de Mercado] < rango_VM1, pond_VM1,

if([Valor de Mercado] < rango_VM2, pond_VM2, pond_VM3))

Re: Calculating with case statement problems

Fri, 07 Mar 2014 11:33:22 GMT

Hi Sena,

If you are trying to use a case statement like approach its better for you to do this in script with a mapping table

i.e.

MappingTable:

MAPPING LOAD

[Tipo de Encargo],[Mapping]

FROM....

In your load statement write this expression

applymap('MappingTable',[Tipo de Encargo],[Tipo de Encargo]) as [Tipo de Encargo]

An alternative is to use a pick(match()) function as opposed to using a lot of if statements

i.e. pick(match([Tipo de Encargo],1,2,3,4,5,6.....),'One','Two','Three','Four'......)

Re: Calculating with case statement problems

Fri, 07 Mar 2014 11:57:54 GMT

Dear Byron, thanks for your reply.

The "if statements" bucle are working nicely, so I really don't need to change it if it is not necessary.

I also can't use applymap in scripting as there is lot of scripts in the project, as far as the variables "pond_XXXX" are customized by the user.

My only problem is about the results, as they are correct only depending on the graphic used. In a flat table with every dimension expanded, the calculation works perfectly.

Sorry, but i am afraid i can't explain better with that, not a pro in Qlikview neither in English.