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.
Staffan, this is my full table:
PRO_CDGRU AS %PRO_CDGRU,
PRO_CDCOR AS %PRO_CDCOR,
PRO_CDEST AS %PRO_CDEST,
PRO_CDMAR AS MAR_CDMAR,
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,
AutoNumber(PRO_CDPRO) AS KEY
PED_NUMPED AS PED_STATUS,
PED_SEQ AS %PED_SEQ,
AutoNumber(PED_NUMPED) AS KEY
**are many tables, I need to create a link table to get the search results you need.
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.
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
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.