Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
H Pradeep, Try to concatenate the below fields
MODEL_ID | EQUIPMENT_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,
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;
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
I am not sure how you are linking your tables.. Can you share the script which you are using?
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.
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
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
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
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