topic Best data model for finite states date transitions in App Development
https://community.qlik.com/t5/App-Development/Best-data-model-for-finite-states-date-transitions/m-p/1664112#M49951
<P>Hello, I would like to visualize the number of documents in a particular state by date.</P><P>My documents have daily changes to their states and I have a log table with the start date & end date (null if this is the current state).<BR />The table structure is this:</P><DIV class="mceNonEditable lia-copypaste-placeholder"> </DIV><TABLE><TBODY><TR><TD>fileId</TD><TD>state</TD><TD>start date</TD><TD>end date</TD></TR><TR><TD>1000</TD><TD>-1</TD><TD>STARTDATE</TD><TD>01/01/2020 08:29</TD></TR><TR><TD>1000</TD><TD>0</TD><TD>01/01/2020 08:29</TD><TD>03/01/2020 12:15</TD></TR><TR><TD>1000</TD><TD>1</TD><TD>03/01/2020 12:15</TD><TD>06/01/2020 14:17</TD></TR><TR><TD>1000</TD><TD>2</TD><TD>06/01/2020 14:17</TD><TD>07/01/2020 10:44</TD></TR><TR><TD>1000</TD><TD>3</TD><TD>07/01/2020 10:44</TD><TD>NULL</TD></TR><TR><TD>2000</TD><TD>-1</TD><TD>STARTDATE</TD><TD>01/01/2020 11:07</TD></TR><TR><TD>2000</TD><TD>0</TD><TD>01/01/2020 11:07</TD><TD>02/01/2020 17:13</TD></TR><TR><TD>2000</TD><TD>1</TD><TD>02/01/2020 17:13</TD><TD>04/01/2020 14:17</TD></TR><TR><TD>2000</TD><TD>2</TD><TD>04/01/2020 14:17</TD><TD>05/01/2020 10:44</TD></TR><TR><TD>2000</TD><TD>3</TD><TD>05/01/2020 10:44</TD><TD>NULL</TD></TR></TBODY></TABLE><P> </P><P>I would like to write a measure that counts all the files that were in a particular state, for each date value.<BR />I will filter state=1 (for example) and I would like a chart which shows the number of files in state 1 at 01/01/2020, 02/01/2020, ... and so on. I need to visualize the time trend of the various states.<BR /><BR />Can I write a measure or it's better to calculate the scalar product of document states and calendar dates ?</P><P>In SQL I would join calendar table with document state table ON calendar.date between documentState.startDate AND ISNULL(documentState.endDate, GETDATE()). </P><P>Can you help ?</P><P> </P><P> </P>Thu, 09 Jan 2020 14:02:18 GMTDiegoLotti2020-01-09T14:02:18ZBest data model for finite states date transitions
https://community.qlik.com/t5/App-Development/Best-data-model-for-finite-states-date-transitions/m-p/1664112#M49951
<P>Hello, I would like to visualize the number of documents in a particular state by date.</P><P>My documents have daily changes to their states and I have a log table with the start date & end date (null if this is the current state).<BR />The table structure is this:</P><DIV class="mceNonEditable lia-copypaste-placeholder"> </DIV><TABLE><TBODY><TR><TD>fileId</TD><TD>state</TD><TD>start date</TD><TD>end date</TD></TR><TR><TD>1000</TD><TD>-1</TD><TD>STARTDATE</TD><TD>01/01/2020 08:29</TD></TR><TR><TD>1000</TD><TD>0</TD><TD>01/01/2020 08:29</TD><TD>03/01/2020 12:15</TD></TR><TR><TD>1000</TD><TD>1</TD><TD>03/01/2020 12:15</TD><TD>06/01/2020 14:17</TD></TR><TR><TD>1000</TD><TD>2</TD><TD>06/01/2020 14:17</TD><TD>07/01/2020 10:44</TD></TR><TR><TD>1000</TD><TD>3</TD><TD>07/01/2020 10:44</TD><TD>NULL</TD></TR><TR><TD>2000</TD><TD>-1</TD><TD>STARTDATE</TD><TD>01/01/2020 11:07</TD></TR><TR><TD>2000</TD><TD>0</TD><TD>01/01/2020 11:07</TD><TD>02/01/2020 17:13</TD></TR><TR><TD>2000</TD><TD>1</TD><TD>02/01/2020 17:13</TD><TD>04/01/2020 14:17</TD></TR><TR><TD>2000</TD><TD>2</TD><TD>04/01/2020 14:17</TD><TD>05/01/2020 10:44</TD></TR><TR><TD>2000</TD><TD>3</TD><TD>05/01/2020 10:44</TD><TD>NULL</TD></TR></TBODY></TABLE><P> </P><P>I would like to write a measure that counts all the files that were in a particular state, for each date value.<BR />I will filter state=1 (for example) and I would like a chart which shows the number of files in state 1 at 01/01/2020, 02/01/2020, ... and so on. I need to visualize the time trend of the various states.<BR /><BR />Can I write a measure or it's better to calculate the scalar product of document states and calendar dates ?</P><P>In SQL I would join calendar table with document state table ON calendar.date between documentState.startDate AND ISNULL(documentState.endDate, GETDATE()). </P><P>Can you help ?</P><P> </P><P> </P>Thu, 09 Jan 2020 14:02:18 GMThttps://community.qlik.com/t5/App-Development/Best-data-model-for-finite-states-date-transitions/m-p/1664112#M49951DiegoLotti2020-01-09T14:02:18Z