5 Replies Latest reply: Feb 24, 2016 6:00 PM by Marco Wedel RSS

    Computing Age across tables

    Daniel Farrow

      I have two tables, one with personal information, like data of birth.  And another table with transaction date.  They can be joined via a Cust_ID.  Is there a way, during the load process to computer the difference in these two dates for a customer for all of her or his corresponding transactions? 

       

      In other words, I want the age of a person at the time of the transaction, but the two dates are in different tables and doing the age calculation at the time of displaying the chart is unacceptably slow.

       

      Any help would be greatly appreciated.

       

      -Daniel

        • Re: Computing Age across tables
          m w

          If you join load the two tables, you will get a single table. Then you can calculate the date differences.

            • Re: Computing Age across tables
              Daniel Farrow

              Didn’t work.  Here is a snippet of my code.  What it gets to the line with ContactAge, I get an error message about not finding birthday.

               

               

              LOAD CustomerID,

                  Birthday,

                  age(today(),Birthday) as CustAge,

                  Year(Birthday) as BirthYear,

                  date(Birthday) as dob,

                  IDType2;

              SQL SELECT *

              FROM PersonalInfo;

               

               

              Inner Join

              LOAD SessionID,

                  CustomerID,

                  date(SessionDate) as SessDate,

                  age(SessionDate, Birthday) as ContactAge,

                  Reason;

              SQL SELECT *

              FROM Session;

               

               

              Daniel W. Farrow, IV

              DISCLAIMER: This e-mail and any files transmitted with it may contain confidential and/or proprietary information.  It is intended solely for the use of the individual or entity who is the intended recipient.  Unauthorized use of this information is prohibited.  If you have received this in error, please contact the sender by replying to this message and delete this material from any system it may be on.

            • Re: Computing Age across tables
              Marco Wedel

              Hi,

               

              one solution could be:

               

              QlikCommunity_Thread_206869_Pic1.JPG

               

              QlikCommunity_Thread_206869_Pic2.JPG

               

               

               

              tabPersonalInfo:
              LOAD RecNo() as CustomerID,
                   Date(MakeDate(1950)+Ceil(10000*Rand())) as Birthday
              AutoGenerate 10;
              
              tabSession:
              LOAD RecNo() as SessionID,
                   Ceil(Rand()*10) as CustomerID,
                   Date(Today()-Ceil(Rand()*5000)) as SessionDate
              AutoGenerate 50;
              
              Left Join (tabSession)
              LOAD CustomerID,
                   Birthday
              Resident tabPersonalInfo;
              
              Left Join (tabSession)
              LOAD Distinct
                   Birthday,
                   SessionDate,
                   Age(SessionDate,Birthday) as ContactAge
              Resident tabSession;
              
              DROP Field Birthday From tabSession;
              

               

              hope this helps

               

              regards

               

              Marco

              • Re: Computing Age across tables
                Marco Wedel

                another possible solution might be:

                 

                 

                tabPersonalInfo:
                LOAD RecNo() as CustomerID,
                    Date(MakeDate(1950)+Ceil(10000*Rand())) as Birthday
                AutoGenerate 10;
                
                mapPersBirthday:
                Mapping LOAD * Resident tabPersonalInfo;
                
                tabSession:
                LOAD *,
                    Age(SessionDate,ApplyMap('mapPersBirthday',CustomerID)) as ContactAge;
                LOAD RecNo() as SessionID,
                    Ceil(Rand()*10) as CustomerID,
                    Date(Today()-Ceil(Rand()*5000)) as SessionDate
                AutoGenerate 50;
                

                 

                hope this helps

                 

                regards

                 

                Marco