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

[alexandros17]

I think it works, what is the result you expect?

[helen_pip]

Hello

What I hope to achieve is 1 table called main_data_diag which is populated with data from both the

Inpatients_Diagnostics_Weekly

Outpatients_Diagnostics_Weekly

I am also storing the final table into a QVD

I have done some research and am just testing whether this approach will work for me:

http://community.qlik.com/thread/29497

What do you think?

[kouroshkarimi]

Can you try this?....

Qualify*;
main_data_diag:

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

FROM
C:\Inpatients_Diagnostics_Weekly.qvd
(qvd);

unqualify *;

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);

[helen_pip]

Hello

Thank you for your response

Unfortunately this does not work and my main_data_diag now only contains data from "Outpatients"

I am working on the following though, although not perfected at this moment in time

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;

This is not working perfectly, but am playing around with it

[helen_pip]

Hello

Thank you for your response

Unfortunately this does not work and my main_data_diag now only contains data from "Outpatients"

I am working on the following though, although not perfected at this moment in time

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;

This is not working perfectly, but am playing around with it

[helen_pip]

Hello

Thank you for your response

Unfortunately this does not work and my main_data_diag now only contains data from "Outpatients"

I am working on the following though, although not perfected at this moment in time

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;

This is not working perfectly, but am playing around with it..Actually I think this owrking now

[#8]

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:
LOAD
  *
RESIDENT Inpatients_Diagnostics_Weekly;

CONCATENATE (main_data_diag)
LOAD
  *
RESIDENT Outpatients_Diagnostics_Weekly;

DROP TABLES Inpatients_Diagnostics_Weekly, Outpatients_Diagnostics_Weekly;

[#9]

Based on your original post and your response to alexandros you can achieve the end result without the qualify statement. I am guess with this next comment but I believe the problem is that you might need to resident the tables first or use a preceding load. Below are both scripts to try

Simply use
main_data_diag:

LOAD *

WHERE data_source_diag = 'Inpatients';

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

FROM
C:\Inpatients_Diagnostics_Weekly.qvd
(qvd);

tablenamedoesntmatter: // since the tables are exactly the same the tables will concatenate without the need to explicity state concatenate

LOAD *

WHERE data_source_diag = 'Outpatients';

LOAD
'Outpatients' as data_source_diag,

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

FROM
C:\Outpatients_Diagnostics_Weekly.qvd
(qvd);

/**********************************************************************************************/

OR

main_data_diag:

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

FROM
C:\Inpatients_Diagnostics_Weekly.qvd
(qvd);

     main_data_diagFINAL

     LOAD *,

          '1' as Dummy // I am using this instead of NOCONCATENATE because of the next load

     RESIDENT main_data_diag;

     DROP TABLE main_data_diag;

2nd_data_diag:

LOAD

'1' as Dummy,
'Outpatients' as data_source_diag,

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

FROM
C:\Outpatients_Diagnostics_Weekly.qvd
(qvd);

     CONCATENATE (main_data_diagFINAL)

     LOAD *

     RESIDENT 2nd_data_diag;

     DROP TABLE 2nd_data_diag;

     DROP FIELD Dummy;

/*********************************************************************/

[helen_pip]

Hello

Thank you for your response

Unfortunately this doe not work as the error message states:

Table not found

Concatenate(main_data_diag)

LOAD

*

RESIDENT Outpatient_Diagnostics_Weekly

The following does work, but I want to store the qualified data into a QVD.  Now ehen I refresnce the QVD in another document, the QVD has not saved the table name as the prefix and the QVD is just showing the field names

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?