6 Replies Latest reply: Jul 19, 2016 2:54 PM by Robert Winkel RSS

    joining tables

    Robert Winkel

      I have two problems 1 I am converting from desktop Excel  to Enterprise web and direct connect.

      The desktop version links the data like below.  But when I load it into the web version non of the links will work like this.  I have also attached the QVF I created so far on using the direct connect.  The data will load but the sheets will not work.

        • Re: joining tables
          rodrigo silva

          I think witch its realy good concatenate your tables

           

          order entry stat... and HRXS

           

          create a flag indicate what is table, wich this table concatenate you can use your dimensions (combined)

          • Re: joining tables
            rodrigo silva

            Table a

            load key1,

                      key2,

                      key3,

                      key2&'-'&key3 as key_master,

                      field_a,

                      field_b,

                      'table a' as flag

            from souce;

             

            //Table b

            concatenate (Table a)

            load key1,

                      key2,

                      key3,

                      key2&'-'&key3 as key_master,

                      field_1,

                      field_2,

                      'table b' as flag

            from souce;

             

            dimension:

            load key2&'-'&key3 as key_master,

                      key2 as fieldkey1,

                      key3 as fieldkey2

            from source;

              • Re: joining tables
                Robert Winkel

                Can I limit the data to only the data from table1 that matches?

                 

                CONCATENATE

                Table1:
                LOAD *;
                SQL SELECT


                RTSWarnings.PatID AS 'PatID',
                RTSWarnings.RoNo AS 'RoNo'

                FROM Rx.dbo.RTSWarnings RTSWarnings;


                Concatenate
                Table2:

                LOAD *;
                SQL SELECT

                Reorders.PatId as 'PatId',
                Reorders.RoNo as 'RoNo'

                FROM Pat.dbo.Reorders Reorders;

                 

                MAIN

                Table1:

                LOAD *;
                SQL SELECT

                RTSWarnings.FacID AS 'FacID',
                RTSWarnings.PatID AS 'PatID',
                RTSWarnings.RoNo AS 'RoNo',
                RTSWarnings.UserID AS 'UserID',
                RTSWarnings.LastFillDt AS 'LastFillDt',
                RTSWarnings.DaysSupply AS 'DaysSupply',
                RTSWarnings.DueDate AS 'DueDate',
                RTSWarnings.Response AS 'Response',
                RTSWarnings.ReminderSet AS 'ReminderSet',
                RTSWarnings.Reason AS 'Reason',
                RTSWarnings.ReceivedOn as 'ReceivedOn'

                FROM Rx.dbo.RTSWarnings RTSWarnings

                WHERE RTSWarnings.Response = '1' and RTSWarnings.ReceivedOn >= '2016-01-01 00:00:00';


                Table2:

                LOAD *;
                SQL SELECT

                Reorders.DrugLabelName AS 'DrugLabelName',
                Reorders.PatId as 'PatId',
                Reorders.RoNo as 'RoNo'

                FROM Pat.dbo.Reorders Reorders;

              • Re: joining tables
                rodrigo silva

                folow script below

                 

                Table1:

                LOAD *, 'Table1' as  Flag;
                SQL SELECT

                RTSWarnings.FacID AS 'FacID',
                RTSWarnings.PatID AS 'PatID',
                RTSWarnings.RoNo AS 'RoNo',
                RTSWarnings.UserID AS 'UserID',
                RTSWarnings.LastFillDt AS 'LastFillDt',
                RTSWarnings.DaysSupply AS 'DaysSupply',
                RTSWarnings.DueDate AS 'DueDate',
                RTSWarnings.Response AS 'Response',
                RTSWarnings.ReminderSet AS 'ReminderSet',
                RTSWarnings.Reason AS 'Reason',
                RTSWarnings.ReceivedOn as 'ReceivedOn'

                FROM Rx.dbo.RTSWarnings RTSWarnings

                WHERE RTSWarnings.Response = '1' and RTSWarnings.ReceivedOn >= '2016-01-01 00:00:00';

                 

                 

                //Table2:

                concatenate (Table1)

                LOAD *, 'Table2' as  Flag;
                SQL SELECT

                Reorders.DrugLabelName AS 'DrugLabelName',
                Reorders.PatId as 'PatId',
                Reorders.RoNo as 'RoNo'

                FROM Pat.dbo.Reorders Reorders;

                  • Re: joining tables
                    Robert Winkel

                    It still pulls all records from table2  it should only have 30,427

                     

                     

                     

                     

                    LIB CONNECT TO 'pmd-sandbox (pharmore_rwinkel)';

                    Table1:
                    LOAD *, 'Table1' as  Flag;
                    SQL SELECT
                    RTSWarnings.FacID AS 'FacID',
                    RTSWarnings.PatID AS 'PatID',
                    RTSWarnings.RoNo AS 'RoNo',
                    RTSWarnings.UserID AS 'UserID',
                    RTSWarnings.LastFillDt AS 'LastFillDt',
                    RTSWarnings.DaysSupply AS 'DaysSupply',
                    RTSWarnings.DueDate AS 'DueDate',
                    RTSWarnings.Response AS 'Response',
                    RTSWarnings.ReminderSet AS 'ReminderSet',
                    RTSWarnings.Reason AS 'Reason',
                    RTSWarnings.ReceivedOn as 'ReceivedOn'
                    FROM Rx.dbo.RTSWarnings RTSWarnings
                    WHERE RTSWarnings.Response = '1' and RTSWarnings.ReceivedOn >= '2016-01-01 00:00:00';


                    //Table2:
                    concatenate (Table1)
                    LOAD *, 'Table2' as  Flag;
                    SQL SELECT
                    Reorders.DrugLabelName AS 'DrugLabelName',
                    Reorders.PatId as 'PatId',
                    Reorders.RoNo as 'RoNo'
                    FROM Pat.dbo.Reorders Reorders;

                     


                    [autoCalendar]:
                      DECLARE FIELD DEFINITION Tagged ('$date')
                    FIELDS
                      Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),
                      Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter'),
                      Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$axis', '$yearquarter'),
                      Month($1) AS [Month] Tagged ('$month'),
                      Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth'),
                      Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber'),
                      Date(Floor($1)) AS [Date] Tagged ('$date');

                    DERIVE FIELDS FROM FIELDS [ReceivedOn], [LastFillDt], [DueDate] USING [autoCalendar] ;