topic Link table creation in QlikView

Link table creation

Fri, 20 May 2011 13:20:33 GMT

Hello,

I want to know how to create link table using script.

Please find the attached file for more information.

Thanks

Samir

Link table creation

Fri, 20 May 2011 13:53:58 GMT

Hi see this link

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

it might help you.

regards

sravan

Re: Link table creation

Fri, 20 May 2011 14:48:36 GMT

User:
Noconcatenate
Load autonumber(LoginId & Server) as LoginIDServer,
        LoginId,
        Server,
        FullName,
        Emailid
;
LinkTable:
Load Distinct LoginIDServer,
        LoginId,
        Server,
Resident User;
Drop tables LoginId, Server from User;
Project:
Noconcatenate
Load autonumber(Server & Project_Path) as ServerProject_Path,
Project_Path,
Project_Title,
Project_Created_On,
Server
;
Join(LinkTable)
Load Distinct ServerProject_Path
Project_Path,
Server
Resident Project;
Drop Fields Project_Path,Server from Project
;
LoginDetails:
Noconcatenate
Load autonumber(LoginId & Server) as LoginIDServer,
        autonumber(Server & Project_Path) as ServerProject_Path,
LoginId,
Server,
Project_Path,
Access_On,
;
Join(LinkTable)
LoginIDServer,
ServerProject_Path,
LoginId,
Server
Resident LoginDetails
;
Drop tables LoginId,Server from LoginDetails;

This must solve your problem..Notice it is untested...

instead of join may be you have to use concatenate depending on the data!

Regards

Sravan

Link table creation

Sun, 22 May 2011 08:46:07 GMT

Hello Sravan,

Heartiest thanks for quick response.

I tried this but still it is creating one synthetic key (LoginIDServer+ServerProject_Path) and as my data too big, getting failure.

I guessed why it is creating synthetic key: In LinkTable table, after first two join, there are 5 fields:

LoginIDServer,

ServerProject_Path,

LoginId,

Server,

Project_Path

As we go further to join the LoginIDServer, ServerProject_Path from LoginDetails table to LinkTable table, it creates synthetic key.

Can you please guide me how to remove this synthetic key?

Thanks

Samir

Link table creation

Sun, 22 May 2011 10:08:02 GMT

Hello Samir,

QV links tables together using same fieldnames. This is a very powerful and convenient feature of QV but it may also result into funny and unexpected results (data-structures like snyc-tables) if you are not familar with it. So the first try would be to use the qualify - statement before loading the seond table. Or rename the according fields, if you do not want the links.

Second is to look where it is necessary to link tables via keys together. It can be very useful to link tables, best with only one corresponding keyfield in each table.

HtH for the moment

Roland

Re: Link table creation

Mon, 23 May 2011 05:12:57 GMT

Hi Samir,

I think what you need is one more combined Key...

User:
Noconcatenate
Load autonumber(LoginId & Server) as LoginIDServer,
        LoginId,
        Server,
        FullName,
        Emailid
;
LinkTable:
Load Distinct LoginIDServer,
        LoginId,
        Server,
Resident User;
Drop fields LoginId, Server from User;
Project:
Noconcatenate
Load autonumber(Server & Project_Path) as ServerProject_Path,
Project_Path,
Project_Title,
Project_Created_On,
Server
;
Join(LinkTable)
Load Distinct ServerProject_Path
Project_Path,
Server
Resident Project;
Drop Fields Project_Path,Server from Project
;
LoginDetails:
Noconcatenate
Load autonumber(LoginId & Server & Project_Path) as LoginIDServerProjectPath,
        autonumber(LoginId & Server) as LoginIDServer,
        autonumber(Server & Project_Path) as ServerProject_Path,
LoginId,
Server,
Project_Path,
Access_On,
;
Join(LinkTable) distinct
LoginIDServerProjectPath,
LoginIDServer,
ServerProject_Path,
LoginId,
Server
Resident LoginDetails
;
Drop Fields LoginIDServer,ServerProject_Path,LoginId,Server from LoginDetails;

If It still does not help, add an example!

Regards

Sravan

Re: Link table creation

Tue, 24 May 2011 07:23:00 GMT

Hey samir,

Did u solve your Problem??

Test by debugging with less no of records..

Link table creation

Tue, 24 May 2011 08:01:12 GMT

Hi Sravan,

I have not tested yet, busy with other stuff.

I will try it after 3 pm today and update you soon.

Thanks

Samir

Link table creation

Tue, 24 May 2011 10:48:48 GMT

Hi Sravan,

Many-many thanks...

It worked for me.

Now,using this technique I am able to link n number of tables.

Once again, heartiest thanks Sravan.

Thanks

Samir

Link table creation

Tue, 24 May 2011 10:50:48 GMT

Hi Samir,

you are welcome!

Regards

Sravan

Link table creation

Thu, 02 Jun 2011 11:10:38 GMT

Hi Sravan,

I am getting two different value for 'LoginIDServer' for the same LoginId & Server in LinkTable

Thanks

___________________________________________________________________________

User:

Noconcatenate

Load autonumber(LoginId & Server) as LoginIDServer,

LoginId,

Server,

FullName,

Emailid

;

LoginDetails:

Noconcatenate

Load autonumber(LoginId & Server & Project_Path) as LoginIDServerProjectPath,

autonumber(LoginId & Server) as LoginIDServer,

autonumber(Server & Project_Path) as ServerProject_Path,

LoginId,

Server,

Project_Path,

Access_On,

;

LinkTable:

Load Distinct LoginIDServer,

LoginId,

Server,

Resident User;

Drop fields LoginId, Server from User;

***********************************************************************************************

Join(LinkTable) distinct

LoginIDServerProjectPath,

LoginIDServer,

ServerProject_Path,

LoginId,

Server

Resident LoginDetails

;

Drop Fields LoginIDServer,ServerProject_Path,LoginId,Server from LoginDetails;

______________________________________________________________________________________

Link table creation

Thu, 02 Jun 2011 11:54:53 GMT

Hello,

I am generating each table using the script in seperate qvw file & storing the output into seperate QVD file.

Thanks

Samir

Link table creation

Tue, 07 Jun 2011 11:48:52 GMT

Hi Guess,

sorry for the delay in answering.Autonumber generates a automatic number and I am assuming it might be the cause or may be not. Try this

and if it still does not work. Add an example.

User:
Noconcatenate
Load LoginId & '_' & Server) as LoginIDServer,
        LoginId,
        Server,
        FullName,
        Emailid
;
LoginDetails:
Noconcatenate
Load LoginId & '_' & Server & '_' & Project_Path as LoginIDServerProjectPath,
        LoginId & '_' & Server as LoginIDServer,
        Server & '_' & Project_Path as ServerProject_Path,
LoginId,
Server,
Project_Path,
Access_On,
;
LinkTable:
Load Distinct LoginIDServer,
        LoginId,
        Server,
Resident User;
Drop fields LoginId, Server from User;
***********************************************************************************************
Join(LinkTable) distinct
LoginIDServerProjectPath,
LoginIDServer,
ServerProject_Path,
LoginId,
Server
Resident LoginDetails
;
Drop Fields LoginIDServer,ServerProject_Path,LoginId,Server from LoginDetails;

Hope it helps

Sravan

Re: Link table creation

paulyeo11 — Thu, 20 Jun 2013 07:06:58 GMT

very good post.