Column name after left join contains "-1"

[Anonymous]

Hello,

I have table T with many columns. After loading T I aggregate some number for each record  - count of records in other table. I can create new column in T with this number MyNumber:

T:

LOAD

  Rada&Ci as ID_Dod,

  .

  .

SQL SELECT * FROM ...;

Left Join (T)

LOAD Rada&Ci as ID_Dod,

         CountOfRec as T.MyNumber;

SQL SELECT D.Rada,D.Ci,Count(*) as CountOfRec

FROM ...

WHERE ...

GROUP BY ....

I can have table T with columns T.ID_DOD, T. ... and T.MyNumber, but QV after reload show T.ID_DOD, ..., T.T-1.MyNumber

Please, where I'm doing mistake? It's problem with QUALIFY/UNQUALIFY?

Thanks,

Olda

[kaushiknsolanki]

HI,

     If you see your data model, these both tables are not linked with each other.

     What happens when you use qualify is, the table field name changes (i.e Tablename.FieldName) format.

     So whenever you are joining table with a qualified table, you should give linking field name in qualified field name structure. Meaning in your case the  linking field name should be T.ID_Dod.

     Try this script.

Qualify *;

T:

LOAD

  Rada&Ci as ID_Dod,

  .

  .

SQL SELECT * FROM ...;

Left Join (T)

LOAD Rada&Ci as T.ID_Dod,

         CountOfRec as T.MyNumber;

SQL SELECT D.Rada,D.Ci,Count(*) as CountOfRec

FROM ...

WHERE ...

GROUP BY ....

Regards,

Kaushik Solanki

[Anonymous]

Hello Kaushik,

I forgot to write, the commands

QUALIFY *;

UNQUALIFY ... ,ID_Dod,ID_Vyd,ID_Prev,ID_Sarze,...;

precedes.

If I try QUALIFY *; before loading T, I give both columns with -1:

T-1.ID_Dod

T-1.MyNumber

Do you have any other idea?

Thanks,

O.

[kaushiknsolanki]

See the reason is the fields from second table which is not used as linking field, is still qualified and because you are joining that table with other table, by default name given will be first table name -1.

What is the problem you are facing due to this?

Regards,

Kaushik Solanki