joining tables

[#1]

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.

[#2]

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)

[#3]

Can you give me an example?

[#4 · Accepted solution]

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;

[#5]

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;

[#6]

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;

[#7]

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] ;