7 Replies Latest reply: Jan 4, 2015 8:52 PM by xuxuma xixuma RSS

    Link table

    Alessandra Coelho

      Can someone help me with this link table, where I am going wrong?

       

      PRODUTOS:

      LOAD

           PRO_SITUACAO,

           PRO_TIPO ,

           more...

           AutoNumber(PRO_CDPRO) AS KEY

        

      FROM

      [..\1-Extracao\EXT\TABPRECOS.qvd]

      (qvd);

       

       

      PEDIDOS:

      LOAD PED_ANO,

           PED_CCUSTO,

           PED_CDCLIFOR,

           PED_CDEMP,

           PED_CDREP,

           more..

           AutoNumber(PED_NUMPED) AS KEY

       

      FROM

      [..\1-Extracao\EXT\EXT_PEDIDOS.qvd]

      (qvd);

       

      *****************************************************************

      LINKTABLE:

      LOAD Distinct

        AutoNumber(PRO_CDPRO) AS KEY,

        PRO_CDPRO

      Resident PRODUTOS;

       

      Concatenate(Linktable)

      LOAD Distinct

        AutoNumber(PED_NUMPED) AS KEY,

        PED_NUMPED

      Resident PEDIDOS;

        • Re: Link table
          Ajay Krishnan Prabhakaran

          Hey Alessandra,

          A few questions. Are there any common key fields between those two tables. You can't concatenate them the way its done as there are not common fields that I see. If there was a common key you can join instead of concatenate.

          Also see if Link table is absolutely necessary. It may not be required sometimes. It totally depends on the tables you have.

          Hope it helps.

          Thanks

          • Re: Link table
            Staffan Johansson

            Hi in your link table us KEY because you read Resident, I can not see in your sample if you have the field PRO_CDPRO. If you dont you can not use it in your Linktable:

             

            LINKTABLE:

            LOAD distinct

            KEY

            Resident PRODUTOS;

             

            concatenate

            LOAD distinct

            KEY

            Resident PEDIDOS;

              • Re: Link table
                Alessandra Coelho

                Staffan, this is my full table:

                 

                PRODUTOS:

                LOAD 

                     PRO_CDGRU AS %PRO_CDGRU,

                     PRO_CDCOR AS %PRO_CDCOR,

                     PRO_CDEST AS %PRO_CDEST,

                     PRO_CDMAR AS MAR_CDMAR,

                     PRO_CDPRO,

                     PRO_CDSGRU AS %PRO_CDSGRU,

                     PRO_CDTAM AS %PRO_CDTAM,

                     PRO_CDTEC AS %PRO_CDTEC,

                     PRO_CDTPC AS %PRO_CDTPC,

                     PRO_DESCRPRO AS %PRO_DESCRPRO,

                     PRO_ALTURA,

                     PRO_ATIPROD,

                     PRO_BASE,

                     PRO_CASAQTD,

                     PRO_CASAVLR,

                     PRO_CDAUX,

                     PRO_CDBARRAS,

                     PRO_CDCFI,

                     PRO_CDCLIFOR,

                     PRO_CDCOMP, 

                     PRO_CDDES, 

                     PRO_CDGEN,

                     PRO_CDGNR,

                     PRO_CDMAP, 

                     PRO_CDMOL, 

                     PRO_CDST, 

                     PRO_CDTPP,

                     PRO_CODST,

                     PRO_COMISSAO,

                     PRO_COMPR,

                     PRO_CORREDOR,

                     PRO_DESCRFOR,

                     PRO_ESTMINIMO,

                     PRO_ETIQUETA,

                     PRO_EXTRA,

                     PRO_FABRIC,

                     PRO_FOTO,

                     PRO_GAVETA,

                     PRO_IMPOSTOS,

                     PRO_IPI,

                     PRO_LARGURA,

                     PRO_LOCAL,

                     PRO_LUCRO,

                     PRO_M3,

                     PRO_NATOPE,

                     PRO_PESOBRUTO,

                     PRO_PESOLIQ,

                     PRO_PONTOS,

                     PRO_PRATELEIRA,

                     PRO_PROCESSO,

                     PRO_PRODUCAO,

                     PRO_QTDCARGA,

                     PRO_QTDEMB,

                     PRO_QTDVOL,

                     PRO_REFST,

                     PRO_RENDIMENTO,

                     PRO_SITUACAO,

                     PRO_TIPO ,

                     PRO_TPCOMIS,

                     PRO_UN,

                     PRO_USR,

                     AutoNumber(PRO_CDPRO) AS KEY

                   

                    

                FROM

                [..\1-Extracao\EXT\EXT_PEDIDOS.qvd]

                (qvd);

                *******************************************************

                PEDIDOS:

                LOAD PED_ANO,

                     PED_CCUSTO,

                     PED_CDCLIFOR,

                     PED_CDEMP,

                     PED_CDREP,

                     PED_CDTRANSP,

                     PED_CDVEN,

                     PED_CID,

                     PED_COMIS,

                     PED_COMISREP,

                     PED_CONDPG,

                     PED_CONTATOCLI,

                     PED_CONTATOREP,

                     PED_DESCONTO,

                     PED_DTACAB,

                     PED_DTENT,

                     PED_DTENTREGA,

                     PED_DTEXP,

                     PED_DTPED,

                     PED_DTPROD,

                     PED_DTREPASSE,

                     PED_END,

                     PED_EXPORTA,

                     PED_FAIXA,

                     PED_FERIADO,

                     PED_FINCOMREP,

                     PED_FINCOMVEN,

                     PED_FLGCOMREP,

                     PED_FLGCOMVEN,

                     PED_FONE,

                     PED_FRETE,

                     PED_HORARIODESCARGA,

                     PED_MATCLIENTE,

                     //PED_NUMPED,  

                     PED_NUMPED AS PED_STATUS,

                     PED_OBS,

                     PED_OBSDESCARGA,

                     PED_ORCPED,

                     PED_PERDESC1,

                     PED_PERDESC2,

                     PED_PERDESC3,

                     PED_PERDESC4,

                     PED_PERDESC5,

                     PED_PERDESC6,

                     PED_PERFIN,

                     PED_PERFRETE,

                     PED_PRAZOMEDIO,

                     PED_QTDBORDADO,

                     PED_RESTDESCARGA,

                     PED_SEQ AS %PED_SEQ,

                     PED_SEQTAB,

                     PED_TPPED,

                     PED_UF,

                     PED_USUARIO,

                     PED_VALIDADE,

                     PED_VLRBORDADO,

                     PED_VLRCOMREP,

                     PED_VLRCOMVEN,

                     PED_VLRFRETE,

                     PED_VLRTOT,

                     AutoNumber(PED_NUMPED) AS KEY

                FROM

                [..\1-Extracao\EXT\EXT_PEDIDOS.qvd]

                (qvd);

                 

                **are many tables, I need to create a link table to get the search results you need.

                  • Re: Link table
                    Staffan Johansson

                    Hi as Marco says, maybe there is no need for linktable. Linktable is used if you have several fact tables that cant be connected either by concatenate or join or qlikviewassociated with several concatenated field as a key. if you still need a linktable, move all your keys from all facttable into the linktable by a resident load. The linktable should have all keys to all dimensiontables.

                • Re: Link table
                  Marco Wedel

                  as far as I can see, there are several issues with your script:

                   

                  - PED_NUMPED and PRO_CDPRO need to have comon key values, check if that's the case.

                  - you do not need to include both AutoNumber(PED_NUMPED) and PED_NUMPED in your link table as there will always be the same unique autonumber value for each distinct PED_NUMPED value.

                  - Same for AutoNumber(PRO_CDPRO) and PRO_CDPRO

                  - Concatenating the two subtables to one link table does not make any sense, you will not get any common records linking to both tables this way. Instead of concatenating you would have to join the tables

                  - If PED_NUMPED and PRO_CDPRO have common values which can be used to link the tables 1:1 then there would be no need for any link table. Generating the autonumber key field in both tables would be sufficient to link the tables.


                  To clarify these questions please upload sample data for both tables.


                  hope this helps


                  regards


                  Marco

                  • Re: Link table

                    The data model should dictate the manipulation. 


                    There is no common key field between the tables and you may be trying to create one using a key table. 

                         - Generally the only time to use a key field is when you have multiple fact tables; and even then, concatenate is      often the better option.

                         - The 'Key' fields you created may not represent what you are looking for -- autonumber doesn't magically create a      key.  Look into creating a composite/generic key if you need.

                     

                    The ideal here will be to 'link' AKA 'SQL join' the tables via a single key or value.