joining tables 2 SQL 1 excel

[#1]

I need help in joining tables 2 SQL and 1 Excel file

Three tables with PhrNPI and DateRange   combined hrxs and orderentry

Two tables with RxNo   hrsx and orderentry

is this possible?

[phaneendra_kunc]

You can try creating a concatenated key.

Try attached script.

[#3]

don't see attached?

[phaneendra_kunc · Accepted solution]

  [combined]:

LOAD

  //[Date Range] AS [DateRange],

    [FWUserID]&[Date Range] as KEY1,

  [Department Code] AS [Departmentcode],

  [Department Desc] AS [DepartmentDesc],

  [es] AS [es],

  [Hours] AS [Hours],

  //[FWUserID] AS [PhrNPI]

    ;

LOAD

    [Date Range],

  [Department Code],

  [Department Desc],

  [es],

  [Hours],

  [FWUserID]

FROM [lib://rbw (pharmore_rwinkel)/Paycom Hours 2016.xlsx]

(ooxml, embedded labels, table is combined);

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

[hrxs]:

LOAD *,

PhrNPI&DayName(InitReview) as KEY1,

PhrNPI&DayName(InitReview)&RxNo as KEY2;

DayName(InitReview) as DateRange;

SQL SELECT

InitReview,

Checked,

PhrNPI,

CheckedBy,

DelivDate,

DelivID,

DispenseDt,

DrugLabelName,

Entered,

EnteredByNPI,

eRxMsgID,

FacID,

InvCost,

IVType,

LabelPrintedBy,

LabelPrintedOn,

Lables,

MOP,

MOP2,

NDC,

NewRx,

PackBy,

Packed,

PatID,

PatientPayAmt,

Payor1PaidAmt,

Payor2PaidAmt,

PharmID,

PhName,

PlacedInTote,

PlacedInToteBy,

PriceCd,

RxNo,

TherapeuticInterchange,

TransType,

TtlPrice

FROM Rx.dbo.HRxs

WHERE FacId <> 'FORMS''MSTR''RETAIL''SAMPLE''TEST''TEST#2''TEST2''TEST3''TRLK''stag' and TransType <> 'Q''R' and Entered >= '2016-01-01 00:00:00';

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

KeyId:

LOAD NDC,

     DeaClass,

     RxOtcInd;

SQL SELECT NDC,

     DeaClass,

     RxOtcInd

FROM Drug.dbo.KeyIdentifiers;

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

OrderEntry:

LOAD *,

//DayName(oepInitReview) as DateRange,

PhrNPI&DayName(oepInitReview)&RxNo as KEY2;    

SQL SELECT

    FacId AS OEPFacId,

    RxNo as RxNo,

    DispenseDt as OEPDispenseDt,

    PatId AS OEPPatId,

    TransType AS OEPTransType,

    ProfileOnly AS OEPProfileOnly,

    PhrNPI AS PhrNPI,

    InitReview as oepInitReview,

    EnteredByNPI AS OEPEnteredByNPI,

    Entered AS OEPEntered,

    Deleted AS OEPDeleted

FROM Rx.dbo.OrderEntryStats

WHERE

ProfileOnly = '1' and Entered >= '2016-01-01 00:00:00' and FacId <> 'FORMS''MSTR''RETAIL''SAMPLE''TEST''TEST#2''TEST2''TEST3''TRLK''stag'; 

Drop  RxNo from  OrderEntry;

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

[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 [DateRange], [LabelPrintedOn], [Packed], [PlacedInTote], [DelivDate], [InitReview], [Entered], [DispenseDt], [Checked], [OEPEntered],

[OEPInitReview], [OEPDispenseDt], [OEPDeleted] USING [autoCalendar] ;

[#5]

first try  lol

[phaneendra_kunc]

seems like there is a comma for the first load statement..

repalce with this..

[combined]:

LOAD

  //[Date Range] AS [DateRange],

    [FWUserID]&[Date Range] as KEY1,

  [Department Code] AS [Departmentcode],

  [Department Desc] AS [DepartmentDesc],

  [es] AS [es],

  [Hours] AS [Hours]

  //[FWUserID] AS [PhrNPI]

    ;

LOAD

    [Date Range],

  [Department Code],

  [Department Desc],

  [es],

  [Hours],

  [FWUserID]

FROM [lib://rbw (pharmore_rwinkel)/Paycom Hours 2016.xlsx]

(ooxml, embedded labels, table is combined);

[#7]

Figured the last one out.

[phaneendra_kunc]

Seems like i have misplaced the semi coln. try this

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

[hrxs]:

LOAD *,

PhrNPI&DayName(InitReview) as KEY1,

PhrNPI&DayName(InitReview)&RxNo as KEY2,

DayName(InitReview) as DateRange;

[#9]

I have ben working on this all week so close Thank you almost there

[phaneendra_kunc]

Drop  Field RxNo from  OrderEntry;