topic Re: Qualify a concatenated table in QlikView

Qualify a concatenated table

helen_pip — Fri, 19 Apr 2013 08:24:58 GMT

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

Re: Qualify a concatenated table

alexandros17 — Fri, 19 Apr 2013 08:32:20 GMT

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

Re: Qualify a concatenated table

helen_pip — Fri, 19 Apr 2013 08:39:26 GMT

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?

Re: Qualify a concatenated table

kouroshkarimi — Fri, 19 Apr 2013 08:48:59 GMT

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

Re: Qualify a concatenated table

helen_pip — Fri, 19 Apr 2013 09:27:32 GMT

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

Re: Qualify a concatenated table

helen_pip — Fri, 19 Apr 2013 09:37:42 GMT

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

Re: Qualify a concatenated table

helen_pip — Fri, 19 Apr 2013 09:38:10 GMT

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

Re: Qualify a concatenated table

Fri, 19 Apr 2013 10:01:03 GMT

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;

Re: Qualify a concatenated table

Fri, 19 Apr 2013 10:11:49 GMT

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

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

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;

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

Re: Qualify a concatenated table

helen_pip — Fri, 19 Apr 2013 10:14:23 GMT

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?

Re: Qualify a concatenated table

helen_pip — Fri, 19 Apr 2013 10:33:07 GMT

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?

Re: Qualify a concatenated table

kaushiknsolanki — Fri, 19 Apr 2013 10:44:24 GMT

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

Re: Qualify a concatenated table

Fri, 19 Apr 2013 10:44:48 GMT

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

Re: Qualify a concatenated table

Fri, 19 Apr 2013 11:31:55 GMT

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;

Re: Qualify a concatenated table

atsushi_saijo — Fri, 05 Dec 2014 10:45:25 GMT

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;

Re: Qualify a concatenated table

luciozanarotto — Fri, 18 Sep 2020 05:10:11 GMT

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;