topic Scripting help in QlikView

Scripting help

Thu, 15 Sep 2011 13:50:42 GMT

Hi All,

I have got a two table Transaction and NameAddress. Three columns in Transaction table link to same column in NameAddress table. The sample table structures are given below.

Transaction:

------------------

TransactionID

ClientID

UnderwriterID

ThirdPartyID

Amount

NameAddress:

---------------------

NameID

FullName

Address

other columns

I need to link ClientID with NameID to fetch client name & address details. The same is the case with Underwriter and Thirdparty.

Currently in QlikView I am creating 3 copies of the NameAddress table and joining separately. Is this is best way or there are any other ways to join this?

If having 3 tables is the best way, then the issue I am having is

1. User wants a list box with list of all names. And when he selects a name, data should be displayed if any of Client, Underwriter or Thirparty name matched with selected name from the list box?

Could you plese advice me on how to proceed with this?

Regards,

Murali

Re: Scripting help

gandalfgray — Thu, 15 Sep 2011 14:17:28 GMT

Hi Murali

I think that's a good approach you started with (3 copies of the NameAddress table).

(Have a look in the first "More like this" thread here to the right.)

For the search functionality you want, maybe you can do it with a search object, I am not sure.

Otherwise you may create a new table (TransNames) which links to Transaction on TransactionID:

Assuming you first have left joined so that you have the three different types of fullnames in the Transaction table

TransNames:

Load TransactionID,

ClientFullName As AnyFullName

Resident Transaction;

Concatenate (TransNames)

Load TransactionID,

UnderwriterFullName As AnyFullName

Resident Transaction;

Concatenate (TransNames)

Load TransactionID,

ThirdPartyFullName As AnyFullName

Resident Transaction;

Now your users can search for '*Koti*' in the AnyFullName field to get all transactions where a person with Koti in the name has been involved in any way (as Client, Underwriter and/or ThirdParty)

hth

/gg

Re: Scripting help

Thu, 15 Sep 2011 14:26:52 GMT

Thanks a lot for your post.

This is exactly how I am currently hadling it in my script. Only change is instead of TransactionID, I am using RowNo() in my script.

But the key issue I am facing here is, because the NameAddress table has close to 2M records, having 3 copies of it and again concatenating to create a new table is taking quite a lot of time to load data and also more RAM space when users access this using access point.

I posted this here to find out what I am doing is correct or there any other better ways to handle this.

Scripting help

jonathandienst — Thu, 15 Sep 2011 14:31:08 GMT

Murali

Need som more info on your data model:

Are the UnderwriterID and Thirdparty different for the ClientID in the two tables? If they are, then I would rename them as something different in one of the two tables and link only on the ClientID.

Eg LOAD UndewriterID AS ClientUnderwriterID, Thirdparty AS ClientThirdparty.... for NameAddressData

If the ClientID can have transactions with many combinations of UnderwriterID and Thirdparty, (ie the key to the transaction table is ClientID, UnderwriterID and Thirdparty), then you need to decide whether you need the UnderwriterID and Thirdparty in the NameAddress table - ie what purpose do they serve?

If you need to link on all three fields (ie the key to the relationship is all three fields, you can allow QV to create a synthetic key, or create your own composite key. Another option is to merge the two tables to create a single fact table. The best option depends on your data model and desgin objectives.

Remember that QV creates an association between two tables by using a field of the same name in the two tables, so you would use:

LOAD NameID as ClientID .... for the NameAddress data

Hope these help

Jonathan

Re: Scripting help

gandalfgray — Thu, 15 Sep 2011 14:37:00 GMT

Hi again

You may consider changing your datamodel so you have

Transaction

Transaction_ID

Amount

Agent

Transaction_ID

Type (here you will have Client/Undertaker/Thirdparty)

NameID

NameAddress

NameID

FullName

Address

other columns

Transaction to Agent is a many-to-many relation

If your users now search in FullName they will get all kinds of Client/Undertaker/Thirdparty)

If you also give them the option to choose Type they can (if they like) specify what they are searching for

hth

/gg

Re: Scripting help

Thu, 15 Sep 2011 14:52:52 GMT

Hi gg,

I think this solution is going to work.

I will try this and let you know the results.

Regards,

Murali

Scripting help

Thu, 15 Sep 2011 15:18:05 GMT

Hi GandalfGray,

I like your solution for many-to-many relationships and will check it for usability within some other circumstances. Btw: do you think Undertakers and underwriters are the same 😉

Regards,

Roland

Re: Scripting help

gandalfgray — Fri, 16 Sep 2011 06:33:45 GMT

"... Undertakers and underwriters ..."

Oh, there I exposed myself as a non-native english-speaker . I was in a hurry, if that's an excuse ...

/gg