Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

joining tables 2 SQL 1 excel

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?

Tags (1)
1 Solution

Accepted Solutions
phaneendra_kunc
Valued Contributor III

Re: joining tables 2 SQL 1 excel

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

22 Replies
phaneendra_kunc
Valued Contributor III

Re: joining tables 2 SQL 1 excel

You can try creating a concatenated key.

Try attached script.

Not applicable

Re: joining tables 2 SQL 1 excel

don't see attached?

phaneendra_kunc
Valued Contributor III

Re: joining tables 2 SQL 1 excel

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

Not applicable

Re: joining tables 2 SQL 1 excel

first try  lol

phaneendra_kunc
Valued Contributor III

Re: joining tables 2 SQL 1 excel

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

Not applicable

Re: joining tables 2 SQL 1 excel

Figured the last one out.

phaneendra_kunc
Valued Contributor III

Re: joining tables 2 SQL 1 excel

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;

Not applicable

Re: joining tables 2 SQL 1 excel

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

phaneendra_kunc
Valued Contributor III

Re: joining tables 2 SQL 1 excel

Drop  Field RxNo from  OrderEntry;