topic Re: Link table in QlikView

Link table

Wed, 07 May 2014 19:20:05 GMT

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

Wed, 07 May 2014 19:37:55 GMT

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

stabben23 — Wed, 07 May 2014 19:40:22 GMT

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

MarcoWedel — Wed, 07 May 2014 20:23:01 GMT

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

Wed, 07 May 2014 20:29:59 GMT

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

stabben23 — Wed, 07 May 2014 21:41:52 GMT

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

Sun, 04 Jan 2015 22:27:06 GMT

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.

Re: Link table

Mon, 05 Jan 2015 01:45:29 GMT

i think so

LOAD distinct

KEY

pin sac du phong, pin sac du phong sony, lap dat phong net, thanh ly phong net, mua ban rao vat can tho, tim viec lam can tho, bat dong san can tho