Qualify a concatenated table

[helen_pip]

Hello

I am Qualifying some tables but I have a concatenated table

I have put the following but it does not seem to work and only picks up the first table

Qualify*;
main_data_diag:

LOAD
'inpatients' as data_source_diag,
applymap('MAPPING', [specialty]) AS SPEC

FROM
C:\Inpatients_Diagnostics_Weekly.qvd
(qvd);

Qualify*;

Concatenate (main_data_diag)LOAD
'Outpatients' as data_source_diag,

applymap('MAPPING', [referral_specialty_code]) AS SPEC

FROM
C:\Outpatients_Diagnostics_Weekly.qvd
(qvd);

How do I get the qualify to work on a concatenated table?  I have tried taking out the Qualify*; and I still do not get the desired result

Any ideas?

Thanks

Helen

[helen_pip]

Hello

Thank you for your response

I definately need the qualify statement as I have many other tables with the same field names

I have however got the following working, but what I am now struggling with now is putting the QVD in the right place.....as when I now reference the QVD in another QVW the table name is no longer prefixed in front of the fields

main_data_diag:

LOAD
'inpatients' as data_source_diag,
applymap('MAPPING', [specialty]) AS SPEC

FROM
C:\Inpatients_Diagnostics_Weekly.qvd
(qvd);

Concatenate (main_data_diag)LOAD
'Outpatients' as main_data_diag.data_source_diag,

applymap('MAPPING', [referral_specialty_code]) AS main_data_diag.SPEC

FROM
C:\Outpatients_Diagnostics_Weekly.qvd
(qvd);

Qualify *;
noconcatenate

main_data_diag:
load * resident main_data_diag;
store main_data_diag into C:\QLIKVIEW\DEVELOPMENT\HP\QVD\main_data_diag.qvd (qvd);
drop table main_data_diag;

Do you know where I should store the QVD in this above statement? so it picks up the quailified table?

[kaushiknsolanki]

Hi,

     Try this.

     main_data_diag:

     LOAD
     'inpatients' as data_source_diag,
     applymap('MAPPING', [specialty]) AS SPEC

     FROM
     C:\Inpatients_Diagnostics_Weekly.qvd
     (qvd);

     Concatenate (main_data_diag)LOAD
     'Outpatients' as data_source_diag,

     applymap('MAPPING', [referral_specialty_code]) AS SPEC

     FROM
     C:\Inpatients_Diagnostics_Weekly.qvd
     (qvd);

     Qualify *;

     Main_Data:

     Load data_source_diag as DATA_SOURCE_DIAG,SPEC resident main_data_diag;

     Drop table main_data_diag;

Regards,

Kaushik Solanki

[#4]

Try this,

main_data_diag:

LOAD
'inpatients' as data_source_diag,
applymap('MAPPING', [specialty]) AS SPEC

FROM
C:\Inpatients_Diagnostics_Weekly.qvd
(qvd);



Concatenate (main_data_diag)LOAD
'Outpatients' as data_source_diag,

applymap('MAPPING', [referral_specialty_code]) AS SPEC

FROM
C:\Outpatients_Diagnostics_Weekly.qvd
(qvd);




Qualify *;
noconcatenate

main_data_diag:
load * resident main_data_diag;
DROP Table main_data_diag;

store main_data_diag into C:\QLIKVIEW\DEVELOPMENT\HP\QVD\main_data_diag.qvd (qvd);
drop table

main_data_diag;

Regards,

Amay

[#5]

Sorry, I mean this :

Try this :

Qualify *;

Inpatients_Diagnostics_Weekly:
LOAD
'inpatients' as data_source_diag,
applymap('MAPPING', [specialty]) AS SPEC
FROM C:\Inpatients_Diagnostics_Weekly.qvd (qvd);

Outpatients_Diagnostics_Weekly:
LOAD
  'Outpatients' as data_source_diag,
  applymap('MAPPING', [referral_specialty_code]) AS SPEC
FROM C:\Outpatients_Diagnostics_Weekly.qvd (qvd);

Unqualify*;

main_data_diag:
NOCONCATENATE LOAD
  *
RESIDENT Inpatients_Diagnostics_Weekly;

CONCATENATE (main_data_diag)
LOAD
  *
RESIDENT Outpatients_Diagnostics_Weekly;

DROP TABLES Inpatients_Diagnostics_Weekly, Outpatients_Diagnostics_Weekly;

[atsushi_saijo]

I just stumbled similar issue, and I did concatenated without qualifying for the specific 2 tables, then:

RENAME TABLE (concatenated table) to tmp;

Qualify *;

NoConcatenate PROD: LOAD * resident tmp;

Unqualify *;

DROP TABLE tmp;

[luciozanarotto]

Is necessary:

1) load first table 2) load (and concatenate) second table 3) load 'resident' table and here QUALIFY fields.

I hope this help you. Lucio

// uso di CONCATENATE and QUALIFY

TABELLA:
LOAD
CodProd,
QtaVend,
Prz,
Venduto,
DataDocV,
Cliente,
Magazzino
FROM [lib://Test/test.xlsx]
(ooxml, embedded labels, table is Vend);

Concatenate
LOAD
CodProd,
QtaVend,
Prz,
Venduto,
DataDocV,
Cliente,
Magazzino
FROM [lib://Test/test.xlsx]
(ooxml, embedded labels, table is Vend2);

TABELLA2:
Qualify *;
NOConcatenate
LOAD
*
resident TABELLA;

drop table TABELLA;