topic Re: Creation of a table using columns from different tables in QlikView

Creation of a table using columns from different tables

Tue, 26 Mar 2013 16:24:34 GMT

Hell Community member,

I have columns revcode,physname,adm,chargenumber,pattype from table1

and columns

doctorkey,doctorname,license no from from table2

Now I want to create a table3 containing the columns revcode,physname,admn,chargenumber,doctorkey,pattype , is it possible ?

Regards,

Ramesh

Re: Creation of a table using columns from different tables

Tue, 26 Mar 2013 16:44:30 GMT

Yes it is.

you can use concatenate

T1:

load * from T1

concatenate

load * from T2.

But i doubt it will give you something interesting.

I would rather join tables on common fields (may be revcode = doctorkey?)

regards

chris

Re: Creation of a table using columns from different tables

Tue, 26 Mar 2013 17:35:18 GMT

It seems like table 3 is the same as table 1 but with one additional field doctorkey. You can't add only this field from table 2 because you need one more field to make a join.

Re: Creation of a table using columns from different tables

Wed, 27 Mar 2013 16:45:26 GMT

Thanks for your Response Christian Dmitry,

I have created third table using the two table. When I am about to create Qvd, I am getting error of Table not found

Table3. I am attaching my qvw file. Can you please correct me why am i getting this error

Regards

Ramesh

Re: Creation of a table using columns from different tables

Wed, 27 Mar 2013 16:51:54 GMT

Your Table3 has the same set of fields as table Doctors. Therefore it automatically concatenates to existing Doctors table instead of creating a new table. You need to use NOCONCATENATE or make field set (or names) different in Doctors and Table3.

Table3:

NOCONCATENATE LOAD Location,CDK Resident Doctors;

Re: Creation of a table using columns from different tables

Wed, 27 Mar 2013 19:27:05 GMT

Hai Dmitry,

Actually my requirement is, doctorskey is not their in table2, so inorder to bring the doctorkey column into table2 , I am creating a table3 by joing table1 and table2. I Have written the script as above now it is not giving any error, but the table is not created can you say me how to create the table3 from the table1 and table 2

Table3:

LOAD Location,CDK Resident Doctors;

Join

LOAD Location,adm,pat_type,adm_date,phys_name,rev_code,Charge_Number,ATTEND_DOCT_NO Resident [ACP Detail Charges];

I have taken common field Location from both the tables but,the table is not created ,can you say why the table is not being created .

regards

ramesh

Re: Creation of a table using columns from different tables

Wed, 27 Mar 2013 19:34:24 GMT

I don't see field named doctorkey in your script. What field is it?

Re: Creation of a table using columns from different tables

Wed, 27 Mar 2013 19:40:22 GMT

CDK is the doctorkey in the doctors table.

http://community.qlik.com/message/326712#326712

Dmitry, I have kept my data model along with my requirement in the zipped file in the above link

once go through it.Once open it and design the data model for me with patience.

Regards,

Ramesh

Re: Creation of a table using columns from different tables

Wed, 27 Mar 2013 19:48:12 GMT

You can't join CDK by Location as Location has only 3 values and CDK has 1258 values. You need another key field instead of Location which will be unique for each CDK value and will be present in both tables. Otherwise join makes no sense.

Re: Creation of a table using columns from different tables

Wed, 27 Mar 2013 20:10:48 GMT

Yes, I have taken Doctorname in the doctors table1 and physname(table2) and

I have checked it. their is no error but table is not created .

Dmetry, I have another doubt. once you check the data in the ACP detail charges table. many records are repeating for ten times, can you say how to avoid it,

Re: Creation of a table using columns from different tables

Fri, 29 Mar 2013 15:27:46 GMT

Ramesh,

Are you sure Location is your right key to join these 2 tables?

What about ATTEND_DOCT_NO ? Isn't it the same as CDK?

If it is, i would do

Doctors:

LOAD Location,
     CDK
FROM F:\POC_Project\POC\DCCDB_DATA_SDOCTOR.qvd (qvd);

[ACP Detail Charges]:
LOAD
Location,
    adm,
     pat_type,
     adm_date,
     phys_name,
    rev_code,
     Charge_Number,
     ATTEND_DOCT_NO,
     ATTEND_DOCT_NO as ID
FROM F:\POC_Project\POC\vwACPDetail_Charges.qvd (qvd);

Table3:
LOAD Location,CDK, CDK as ID Resident Doctors;
Join
LOAD * Resident [ACP Detail Charges];

If Location is your key, do the same with it instead

regards

Chris