Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

joining tables

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.

1 Solution

Accepted Solutions
Not applicable

Re: joining tables

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;

6 Replies
Not applicable

Re: joining tables

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)

Not applicable

Re: joining tables

Can you give me an example?

Not applicable

Re: joining tables

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;

Not applicable

Re: joining tables

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;

Not applicable

Re: joining tables

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;

Not applicable

Re: joining tables

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

Community Browser