12 Replies Latest reply: Oct 1, 2015 3:31 PM by Janilson Andrade RSS

    Merge Date Dimensions

      Hi guys.

       

      I have a table that stores employees informations. Two of those of these informations are Demission and Admission date.

      I want to create e chart bar report that shows per month the count of demissions and admissions (turn over analysis) and I can't figure out how I can do that.

       

      The first thing I though was to create a load script like the one I post below. But I think it's not a good solution, since it would be a useless table and I would use it only for a chart.

       

      Is there a way to merge those two fields into one and flag them as "admission" ou "demission" just for a chart bar report? Maybe with AGGR function?

      I've searched a lot in forum but I couldn't get a answer.

       

      /*Example Script*/

      [UselessTable]:

      LOAD

          DemissionField as Date,

          'Demission' as Type

          FROM [lib://RH/Dim_Employee.qvd]

      (qvd);

      LOAD

          AdmissionField as Date,

          'Admission' as Type

          FROM [lib://RH/Dim_Employee.qvd]

      (qvd);

       

       

      Thanks all in advance

        • Re: Merge Date Dimensions
          Andy Weir

          What you need to work with is a master calendar to link your 2 dates under one centralised date control.

           

          This link should contain resources to get you started.

           

          Understanding the Master Calendar (video)

           

          There are more if you search for it.

           

          Hope this helps

           

           

          Andy

          • Re: Merge Date Dimensions
            Andy Weir

            That structure you have would work for your case and future use if you add  1 AS ID to it.

             

            Then do something like

             

            Measure = SUM([ID])

            Dimension = Type, Date

             

            In a bar chart

            • Re: Merge Date Dimensions

              You are right, I should use 1 as ID in order to load the table twice (to get two date types).

              I don't understand quite well how a Master Calender would help me (I will watch this video to see if it helps me).

               

              I'm totally lost on it.

                • Re: Merge Date Dimensions

                  The way my data is, I can only have a bar chart with Admission date or Demission date (per month), but not both. Like image below

                   

                  BarChart1Dimension.jpg

                  I want to have 2 bars per month.

                    • Re: Merge Date Dimensions
                      Andy Weir

                      If you have your data structured as above have you tried using set analysis on your measures

                       

                      something like

                       

                      Dimension= Date

                      Measure 1 = # Of Admissions: SUM({<Type={'Admission'}>} ID)

                      Measure 2 = # Of Deadmissions: SUM({<Type={'Demission'}>} ID)

                        • Re: Merge Date Dimensions

                          The problem is that I don't have this structure.

                          Let me show you a sample of my table:

                           

                          EmployeeNameEmployeeIDAdmission DateDemission Date
                          JANILSON301/01/201501/05/2015
                          JOHN405/05/201506/09/2015
                          DEBORA610/05/201509/09/2015
                          MARY810/04/2015

                           

                          My table is more or less like the one above. I have two date dimensions. I want to know the number of admission x demission per month. In order to do that, without creating a new table, I must turn those two fields into one.

                            • Re: Merge Date Dimensions
                              Jonathan Poole

                              Here is a load script that reads the above table in 2 passes to replace the 2 dates with one  'Date' field  and a new 'Type' field which has Admission/Demission as the values.

                               

                              The data above had a blank for the demission date for employee 8. Your actual data source may have nulls in which case you would have to change the where clause below using the isnull() function to check for nulls.

                               

                              Finally , i bucketed all the dates into a MonthYear field which you seem to want

                               

                              Data:

                              Load

                                  EmployeeName,

                                  EmployeeID,

                                  date("Admission Date") as Date,

                                  'Admission' as Type

                              FROM [lib://184795]

                              where  not "Admission Date"='';

                                

                              Load

                                  EmployeeName,

                                  EmployeeID,

                                  date("Demission Date") as Date,

                                  'Demission' as Type

                              FROM [lib://184795]

                              where  not "Demission Date"=''  ;  

                                

                              drop table temp;

                               

                              Dates:

                              Load

                                Date,

                                  MonthName(Date) as MonthYear

                              resident Data;  

                               

                              Capture.PNG

                               

                               

                               

                               

                              Capture2.PNG

                                • Re: Merge Date Dimensions
                                  Jonathan Poole

                                  And forgot... the measure and dimensions in the chart are

                                   

                                  Dimension1:   MonthYear

                                  Dimension2:   Type

                                  Measure:        count(distinct EmployeeID)

                                  • Re: Merge Date Dimensions

                                    I thought about his solution, but I thought it would be a waste of space since I'll duplicate the rows in my table. But, I will use it, maybe it's the only way to do what I want.

                                     

                                    Thanks a lot @Jonathan Pole and @Andy Weir for your replies.

                                     

                                    Thanks community

                                      • Re: Merge Date Dimensions
                                        Andy Weir

                                        No problem Janilson

                                         

                                        Your not duplicating your data you are just separating your facts.

                                        Fact 1 When the Employee was admitted

                                        Fact 2 When the Employee was demitted

                                         

                                        Qliks associative data model loves working with data in this way.  You just need to get comfortable with set analysis and some other techniques to extract the data from this structure and display it in a way your user community wants to see it in.

                                         

                                        Have a look into Star and Snowflake schemas to see why Qlik ideally wants the data structure in this way.

                                         

                                        Regards

                                         

                                         

                                        Andy

                                          • Re: Merge Date Dimensions
                                            Jonathan Poole

                                            Hey guys ! i agree 100% with Andy. 

                                             

                                            Also i wanted to offer up this technical explanation of why more rows can result in a smaller in-memory footprint.

                                             

                                            If you read the following presentation you will see that replacing 2 dates with 1 date will eliminate a symbol table from the model and more than likely result in fewer rows in that same symbol table than the combined rows from the 2 earlier tables. This is because Qlik cares heavily about data uniqueness, and if you use one list to store all the possible dates rather than two, you could reduce (up to half) your storage requirement.  The new symbol table for type will literally only have 2 possible values = 2 rows, not matter how many rows are in there.

                                             

                                            True , the data table does get longer but if there are not too many columns, the bit stuffed pointers that replace the symbol values will not require a very heavy burden.

                                             

                                            WHen you add everything up, extra rows of data can increase the storage, but eliminating uniqueness by consolidating fields and reducing unique values will have the opposite effect, creating a dampening effect of adding more rows .   Its a great scalability model.

                                             

                                            The data scalability of Qlik's in-memory associative data model