14 Replies Latest reply: Mar 8, 2016 4:59 AM by Pradeep Kumar Pandey RSS

    Link Table Problem

    Pradeep Kumar Pandey

      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.

        • Re: Link Table Problem
          hareesh kumar

          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
              Pradeep Kumar Pandey

              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
                  Kaushik Solanki

                  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

                    • Re: Link Table Problem
                      Pradeep Kumar Pandey

                      Its a hierarchical type of data. On selection of Lot Type all product group will be displayed under that lot type. on selection of Product Group all the products will be displayed under that product group and so on.

                        • Re: Link Table Problem
                          Kaushik Solanki

                          Hi,

                           

                          In  the equipment charts, remove the " EquipmentGroupId= "from the expressions.

                           

                          Regards,

                          Kaushik Solanki

                            • Re: Link Table Problem
                              Pradeep Kumar Pandey

                              In the equipment charts there is not equipmentgroupid.

                               

                              On Tue, 08 Mar 2016 14:35:06 +0530 Kaushik Solanki  wrote

                                                                                                              Link Table Problem

                               

                               

                              reply from Kaushik Solanki in QlikView Deployment - View the full discussion

                               

                               

                               

                               

                              Hi,In the equipment charts, remove the " EquipmentGroupId= "from the expressions.Regards,Kaushik

                              Solanki

                               

                               

                               

                                    

                                    

                                    

                                   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
                                  Kaushik Solanki

                                  Hi,

                                   

                                  sum({<TranDate={'>=$(vPeriodFrom)<=$(vPeriodTo)'}, EquipmentGroupId=>} T2.LOT_SIZE_MOVE)

                                   

                                  Above is the expression which I see in your equipment charts.

                                   

                                  I am asking you to remove , EquipmentGroupId= So your expression should be

                                  sum({<TranDate={'>=$(vPeriodFrom)<=$(vPeriodTo)'}>} T2.LOT_SIZE_MOVE)


                                  Regards,

                                  Kaushik Solanki

                                    • Re: Link Table Problem
                                      Pradeep Kumar Pandey

                                      Yes this expression is for equipment group, and I have done that but there is no effect.

                                       

                                      On Tue, 08 Mar 2016 14:49:07 +0530 Kaushik Solanki  wrote

                                                                                                                      Link Table Problem

                                       

                                       

                                      reply from Kaushik Solanki in QlikView Deployment - View the full discussion

                                       

                                       

                                       

                                       

                                      Hi,sum({} T2.LOT_SIZE_MOVE)Above is the

                                      expression which I see in your equipment charts.I am asking you to remove , EquipmentGroupId= So your

                                      expression should besum({} T2.LOT_SIZE_MOVE)

                                      >Regards,Kaushik Solanki

                                       

                                       

                                       

                                            

                                            

                                            

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

                            • Re: Link Table Problem
                              Pradeep Kumar Pandey

                              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
                                  kushal chawda

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

                                    • Re: Link Table Problem
                                      Pradeep Kumar Pandey

                                      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

                                      • Re: Link Table Problem
                                        Pradeep Kumar Pandey

                                        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

                                    • Re: Link Table Problem
                                      Jonathan Dienst

                                      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.