Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
pkpandey
Contributor III
Contributor III

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
Contributor III
Contributor III

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,

Kushal_Chawda

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
Contributor III
Contributor III
Author

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

Kushal_Chawda

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

jonathandienst
Partner - Champion III
Partner - Champion III

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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!