8 Replies Latest reply: Jan 19, 2017 7:14 AM by Koen Bal RSS

    Data model

    juan prado

      Hey Guys, i need some help with this model:

       

      I Have four tables with different information, I attached an excel file with an example.

       

      I need to build a model that will allow me to get a

      • Make in the same line graph which can analyze the billing and collection for different MonthYear -> If i have more than one Collection date take the last date and SUM the Collection Money for that COLLECTION_ID

      For example:

       

       

      IDBILL_IDID_BILL_IDBILL_ISSUE_DATEBILL_ISSUE_DATE_IDBILLED_MONEY
      10054-22610054-2-2601/12/2014241685100

       

       

      IDID_BILL_IDBILL_IDCOLLECTION_DATECOLLECTION_DATE_IDCOLLECTED MONEY
      10054-210054-2-262601/12/2014241682500
      10054-210054-2-262601/01/2015241692600

       

      And take something like this:

       

       

      IDBILL_IDID_BILL_IDBILL_ISSUE_DATEBILL_ISSUE_DATE_IDBILLED_MONEYCOLLECTION_DATECOLLECTED MONEY
      10054-22610054-2-2601/12/201424168510001/01/2015241695100

       

      • Get For each ID_BILL_ID the collected money. Check that in cases i have more than one collection date for the same ID.
      • I think previously i need to group the information in some tables.
      • Make in the same Bar Chart for each MonthYear where i can count the Suscribes, the Unsuscribes for this MonthYear and the Suspension

       

      -> I tried to make a MasterCalendar For each table but i think thats not the best way.

      -> How can i make a good data model for take different indicators, besides those I mention.


      Thanks you in advance for your help!



        • Re: Data model
          rodrigo silva

          I realy like concatenate tables, this way i can share dimensions.

           

          FOLOW THE EXAMPLE OF THE MODEL I THINK IT'S REALY GOOD

           

          USE SET ANALISYS FOR YOUR EXPRESSIONS. EXAMPLE : SUM({<FLAG_FATO={"POL"}>} FIELD)

            • Re: Data model
              juan prado

              Thanks you for your help!

               

              I think this is good, but i need to take something more. How can i create this table from the 2 of the top.

               

              DBILL_ISSUE_DATEBILL_ISSUE_DATE_IDBILLED_MONEY
              10054-22610054-2-2601/12/2014241685100

               

               

              IDID_BILL_IDBILL_IDCOLLECTION_DATECOLLECTION_DATE_IDCOLLECTED MONEY
              10054-210054-2-262601/12/2014241682500
              10054-210054-2-262601/01/2015241692600

               

              And take something like this:

               

               

              IDBILL_IDID_BILL_IDBILL_ISSUE_DATEBILL_ISSUE_DATE_IDBILLED_MONEYCOLLECTION_DATECOLLECTED MONEY
              10054-22610054-2-2601/12/201424168510001/01/20155100
                • Re: Data model
                  rodrigo silva

                  you can use a dinamic table

                  • Re: Data model
                    Mindaugas Bacius

                    One solution might be:

                    map_collected_money:
                    mapping LOAD ID, 
                         sum([COLLECTED MONEY])
                    FROM
                    [https://community.qlik.com/thread/226245]
                    (html, codepage is 1257, embedded labels, table is @5)
                    group by ID;
                    
                    
                    LOAD D, 
                      ApplyMap('map_collected_money', D, ':') as [COLLECTED MONEY],
                         BILL_ISSUE_DATE, 
                         BILL_ISSUE_DATE_ID, 
                         BILLED_MONEY, 
                         F5, 
                         F6
                    FROM
                    [https://community.qlik.com/thread/226245]
                    (html, codepage is 1257, embedded labels, table is @4);
                    

                     

                    The result is:

                    Screenshot_4.jpg

                     

                    Find the attachment.

                      • Re: Data model
                        juan prado

                        Its a good way, if i need the last date collected i have to make a MAX(COLLECTION_DATE) in another mapping load?

                         

                        Thanks!

                          • Re: Data model
                            Mindaugas Bacius

                            Well than instead of ApplyMap use Left join:

                            LOAD D as ID, 
                            // ApplyMap('map_collected_money', D, ':') as [COLLECTED MONEY],
                                 BILL_ISSUE_DATE, 
                                 BILL_ISSUE_DATE_ID, 
                                 BILLED_MONEY
                            FROM
                            [https://community.qlik.com/thread/226245]
                            (html, codepage is 1257, embedded labels, table is @4);
                            
                            
                            left join
                            LOAD ID, 
                                 sum([COLLECTED MONEY]) as [COLLECTED MONEY],
                                 max(date(date#(COLLECTION_DATE, 'DD/MM/YYYY'), 'YYYY-MM-DD')) as COLLECTION_DATE
                            FROM
                            [https://community.qlik.com/thread/226245]
                            (html, codepage is 1257, embedded labels, table is @5)
                            group by ID;
                            

                             

                             

                            Results:

                            Screenshot_4.jpg

                             

                            The attachment also included.

                              • Re: Data model
                                juan prado

                                Thanks again!
                      • Re: Data model
                        Koen Bal

                        Hi Juan,

                         

                        I've created some QvM - Qlikview Modules that I use to help me validate my datamodel.

                        Maybe you'll find them useful.

                         

                        ,KR Koen

                         

                        PS: Don't forget to mark answers as helpful and correct.