Duvida Modelagem

[rhpestana]

Bom dia,

Qual a melhor maneira de tratar na aplicação situação onde o registro pode variar de estado de acordo com a seleção. Por exemplo, uma fatura que em um mês está aberta e em outro fechada???

Teria na fato dois registros dessa mesma fatura, um a para cada referencia ou teria que avaliar ou calcular o status dessa fatura e ver se ela se encaixa na seleção???

Obrigado,

[jsakalis]

Hi Ricardo,

     I am not sure what you are trying to do, but if you are interested in comparing how many bills have been opened in a month against how many have been closed in the same month, you can do it using one record. This assumes that there is a date field for the OpenDate and one for the CloseDate.

     Create a new field, CompareDate, in your LOAD statement that assigns the OpenDate to CompareDate, if the status is OPEN, otherwise assign CloseDate to CompareDate. This way you can use CompareDate as a dimension that will help you count how many of each for every month or specific date.

     If there is no STATUS field, you can create a field in the LOAD using an IF. If the CloseDate  is filled then set to "Closed" otherwise set to "Open". This way you can click on a CompareDate and Open or Closed status to help filter.

I am making some assumptions here and speak no Portuguese.

[rhpestana]

Oi John,

Deu pra entender o que você escreveu, é mais ou menos o que eu estava imaginando... mas por exemplo,

se faço a carga de um 01-jan-2015 e atribuo "Open" para status,  em 01-feb-2015 o registro é alterado para "Close".. ok?

Mas na aplicação, se hoje 18-mar-2015 eu consultar os dados até 31-jan-2015 ele aparecerá como "Close" (quando ainda era "Open")... isso não invalida essa solução? esse teste Open/Close não tem que ser feito dinamicamente?

Obrigado,

[jsakalis]

Ricardo,

Based on what you described to me you would like to know what the status was historically at a point in time. In that case, when you are counting, you would count the number of records (or show records) where the Open date is less than today and less than the Close date, if it is not null. If Close date is null then status should still be open.

This way you would not use a status field to determine if a bill was open during a period of time, you would compare the Open and Close dates. So if you have an OpenDate=01-Jan-2015 and a CloseDate=01-Mar-2015. If you select 31-Jan-2015, because it is between OpenDate and CloseDate, you would count that as 1 in your expression. If you select a date that is greater than 01-Mar-2015, you would count that as 0.

[rhpestana]

John,

Pensei uma coisa diferente, se eu modelar minha tabela fato para ter um registro para "Open" outro para "Close" cada encaixado dentro de usa devida referencia e com um campo para "qtdeOpen" outro "qtdeClose". Como resultado eu consigo o que quero, mas é uma boa solução quanto a modelagem?

obrigado,

[jsakalis]

Ricardo,

     I usually try different methods when building a data model. My first concern is usually getting accurate results, second is performance and third is maintainability. What also is a factor is the amount of data. Some methods work well with smaller data sets, others with very large data sets. For example, if your data set is very large, the approach you suggest is a good one because you will be pre-calculating values. This reduces the complexity of your expressions and speeds up user interface performance.

     In summary, if you are getting accurate results, you have good performance and your script and expressions are easily maintained - then you have succeeded. In QlikView there are many ways to structure your application . There are many good books on data modeling with recommendations on best practices - it all depends on how closely you want or need to follow these.

Regards

[rhpestana]

muito obrigado Jon