Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Link table

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;

Tags (1)
7 Replies
Not applicable

Re: Link table

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

stabben23
Honored Contributor

Re: Link table

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

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

Not applicable

Re: Link table

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.

stabben23
Honored Contributor

Re: Link table

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.

Not applicable

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.

Not applicable

Re: Link table

Community Browser