Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
pkpandey
New Contributor II

Link Table Problem

Hi All

I need help in making a link table. I have two tables with different dates like MOVE_TIME,ARRIVAL_TIME and PERIOD_START and based on these dates I have to generate reports. But how will I make a link table to do that. Please advise Tables are attached. This is urgent.

Thanks in advance.

14 Replies
hareeshkumar_gv
New Contributor III

Re: Link Table Problem

H Pradeep, Try to concatenate the below fields

MODEL_IDEQUIPMENT_NAME

so that sythetic keys will not created, Or else let me know exact you requirement, so that we can associate the two tables, i think concatenate will work,

Re: Link Table Problem

You can do something like below.

I have broken down your MOVE_TIME,ARRIVAL_TIME & PERIOD_START to Separate DATE & TIME Field as Timestamp field occupies more space and also linking on Timestamp field is not advisable. So I have done linking on DATE field which you can use to filter the TIME as well.

Data:

LOAD MODEL_ID,

     LOT_NAME,

     LOT_ID,

     PRODUCT_NAME,

     ROUTE_NAME,

     STEP,

     EQUIPMENT_GROUP_NAME,

     EQUIPMENT_NAME,

     PROCESS_NAME,

     date(Timestamp#(MOVE_TIME,'DD.MM.YYYY hh:mm:ss'),'DD/MM/YYYY') as DATE,

     time(Timestamp#(MOVE_TIME,'DD.MM.YYYY hh:mm:ss'),'hh:mm:ss') as TIME,

     LOT_SIZE,

     STAGE,

     'MOVE_TIME' as DateType,

     'NA' as CYCLE_TIME_TOTAL

FROM

[TABLE.xlsx]

(ooxml, embedded labels, table is TABLE1);

Concatenate(Data)

LOAD MODEL_ID,

     LOT_NAME,

     LOT_ID,

     PRODUCT_NAME,

     ROUTE_NAME,

     STEP,

     EQUIPMENT_GROUP_NAME,

     EQUIPMENT_NAME,

     PROCESS_NAME,

     date(Timestamp#(ARRIVAL_TIME,'DD.MM.YYYY hh:mm:ss'),'DD/MM/YYYY') as DATE,

     time(Timestamp#(ARRIVAL_TIME,'DD.MM.YYYY hh:mm:ss'),'hh:mm:ss') as TIME,

     LOT_SIZE,

     STAGE,

     'ARRIVAL_TIME' as DateType,

     'NA' as CYCLE_TIME_TOTAL

FROM

[TABLE.xlsx]

(ooxml, embedded labels, table is TABLE1);

Concatenate(Data)

LOAD MODEL_ID,

     EQUIPMENT_NAME,

     date(Timestamp#(PERIOD_START,'YYYY-MM-DD hh:mm:ss'),'DD/MM/YYYY') as DATE,

     date(Timestamp#(PERIOD_START,'YYYY-MM-DD hh:mm:ss'),'DD/MM/YYYY') as TIME,

     CYCLE_TIME_TOTAL,

    'PERIOD_START' as DateType

FROM

[TABLE.xlsx]

(ooxml, embedded labels, table is TABLE2);

Calender:

load

DATE,

Year(DATE) as Year,

month(DATE) as Month,

Week(DATE) as Week;

LOAD Distinct DATE

Resident Data;

pkpandey
New Contributor II

Re: Link Table Problem

Many Thanks for replying. My problem is that I have 4-5 tables and I have concatenated like you have

suggested. But I am not getting proper link to the table. So I have tried to create a link table and do

the data model.

Can u please guide me how to create a link table for these two tables.

On Sun, 06 Mar 2016 12:14:39 +0530 Kush141087 wrote

Link Table Problem

reply from Kush141087 in QlikView Deployment - View the full discussion

You can do something like below.I have broken down your MOVE_TIME,ARRIVAL_TIME & PERIOD_START to

Separate DATE & TIME Field as Timestamp field occupies more space and also linking on Timestamp field

is not advisable. So I have done linking on DATE field which you can use to filter the TIME as

well.Data:LOAD MODEL_ID, LOT_NAME, LOT_ID, PRODUCT_NAME, ROUTE_NAME, STEP, EQUIPMENT_GROUP_NAME,

EQUIPMENT_NAME, PROCESS_NAME, date(Timestamp#(MOVE_TIME,'DD.MM.YYYY hh:mm:ss'),'DD/MM/YYYY') as DATE,

time(Timestamp#(MOVE_TIME,'DD.MM.YYYY hh:mm:ss'),'hh:mm:ss') as TIME, LOT_SIZE, STAGE, 'MOVE_TIME' as

DateType, 'NA' as CYCLE_TIME_TOTALFROM[TABLE.xlsx](ooxml, embedded labels, table is

TABLE1);Concatenate(Data)LOAD MODEL_ID, LOT_NAME, LOT_ID, PRODUCT_NAME, ROUTE_NAME, STEP,

EQUIPMENT_GROUP_NAME, EQUIPMENT_NAME, PROCESS_NAME, date(Timestamp#(ARRIVAL_TIME,'DD.MM.YYYY

hh:mm:ss'),'DD/MM/YYYY') as DATE, time(Timestamp#(ARRIVAL_TIME,'DD.MM.YYYY hh:mm:ss'),'hh:mm:ss') as

TIME, LOT_SIZE, STAGE, 'ARRIVAL_TIME' as DateType, 'NA' as CYCLE_TIME_TOTALFROM[TABLE.xlsx](ooxml,

embedded labels, table is TABLE1);Concatenate(Data)LOAD MODEL_ID, EQUIPMENT_NAME, date(Timestamp#

(PERIOD_START,'YYYY-MM-DD hh:mm:ss'),'DD/MM/YYYY') as DATE, date(Timestamp#(PERIOD_START,'YYYY-MM-DD

hh:mm:ss'),'DD/MM/YYYY') as TIME, CYCLE_TIME_TOTAL, 'PERIOD_START' as DateTypeFROM[TABLE.xlsx](ooxml,

embedded labels, table is TABLE2);Calender:loadDATE, Year(DATE) as Year,month(DATE) as Month,Week(DATE)

as Week;LOAD Distinct DATEResident Data;

Reply to this message by replying to this email, or go to the message on Qlik Community

Start a new discussion in QlikView Deployment by email or at Qlik Community

Following Link Table Problem in these streams:

Inbox

© 1993-2016 QlikTech International AB |

Copyright & Trademarks | Privacy | Terms of Use | Software EULA

Re: Link Table Problem

I am not sure how you are linking your tables.. Can you share the script which you are using?

MVP
MVP

Re: Link Table Problem

The DATE field will not link correctly in the above script. You need to add floor() and frac() functions to the date and time expressions:

date(Floor(Timestamp#(MOVE_TIME, 'DD.MM.YYYY hh:mm: ss')),'DD/MM/YYYY') as DATE,

time(Frac(Timestamp#(MOVE_TIME,'DD.MM.YYYY hh:mm:ss')),'hh:mm:ss') as TIME,

(and ditto with PERIOD_START)


The Calendar contains pure dates, so you need to link on pure date values. The format functions Date() and Time() do not convert the underlying values (on which the link happens), they only affect the display format.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
pkpandey
New Contributor II

Re: Link Table Problem

Hi

I am sharing my script. The problem is in link key I think but am not able to correct it. In equipment

group selection only one equipment group should come but here many equipment group is coming. Please

help. This is very urgent.

On Sun, 06 Mar 2016 15:41:43 +0530 Kush141087 wrote

Link Table Problem

reply from Kush141087 in QlikView Deployment - View the full discussion

I am not sure how you are linking your tables.. Can you share the script which you are using?

Reply to this message by replying to this email, or go to the message on Qlik Community

Start a new discussion in QlikView Deployment by email or at Qlik Community

Following Link Table Problem in these streams:

Inbox

© 1993-2016 QlikTech International AB |

Copyright & Trademarks | Privacy | Terms of Use | Software EULA

pkpandey
New Contributor II

Re: Link Table Problem

Hi Kush

Please do something I have already sent you scrambled data file test.qvw for your information and doing

the needful.

On Sun, 06 Mar 2016 15:41:43 +0530 Kush141087 wrote

Link Table Problem

reply from Kush141087 in QlikView Deployment - View the full discussion

I am not sure how you are linking your tables.. Can you share the script which you are using?

Reply to this message by replying to this email, or go to the message on Qlik Community

Start a new discussion in QlikView Deployment by email or at Qlik Community

Following Link Table Problem in these streams:

Inbox

© 1993-2016 QlikTech International AB |

Copyright & Trademarks | Privacy | Terms of Use | Software EULA

pkpandey
New Contributor II

Re: Link Table Problem

Hi

Please find the data model and the application attached. My problem is while selecting the equipment

group button data is not showing properly. I have created Link Table and think that something is wrong

with the composite keys I have made.

On Sun, 06 Mar 2016 16:58:12 +0530 hareesh kumar wrote

Link Table Problem

reply from hareesh kumar in QlikView Deployment - View the full discussion

H Pradeep, Try to concatenate the below fields MODEL_IDEQUIPMENT_NAMEso that sythetic keys will not

created, Or else let me know exact you requirement, so that we can associate the two tables, i think

concatenate will work,

Reply to this message by replying to this email, or go to the message on Qlik Community

Start a new discussion in QlikView Deployment by email or at Qlik Community

Following Link Table Problem in these streams:

Inbox

© 1993-2016 QlikTech International AB |

Copyright & Trademarks | Privacy | Terms of Use | Software EULA

Re: Link Table Problem

Hi,

Can you give an example, what selection you are doing and what output you are getting with what output you are looking.

Regards,

Kaushik Solanki

Community Browser