topic Re: About $syn in QlikView

About $syn

Thu, 05 Apr 2012 09:58:04 GMT

i m trying import the excels of 3 4 tables and i m getting $syn fields and tables created, i have tried concatenate but becuase of concatenate is there any diffrence in the result of join with syn table and with concatenate command?

Please can any one tell me that?

About $syn

vijay_iitkgp — Thu, 05 Apr 2012 10:25:40 GMT

Hi,

Syn table is created because there are more than one field common in your tables.

If your primary key is more than one field then create a concatenated key by using Col1&Col2... and alias the col1 and col2 in any one table. Now you dont have more than one column common so it wont show u syntehetic key.

Hope this will help you.

Re: About $syn

Thu, 05 Apr 2012 10:26:16 GMT

hi,

When you do not specify map conditions, QV will automatically map the fields with same name and make $syn key.

The tables are then mapped with the help of these $syn keys,

but in concatinate and join. syn key is not created but the whole table is merged into one.

In join the matched fields which have same records are merged into one record, i.e no duplicacy.

But in Concatenate, the tableis simply append below another.

Example:

We have two table

A and B

now when we do not use join or concatinate , it will create &syn key:

in join, matching records of common fiels(x and A) will be merged

The result is:

in concatinate , the records are append to the table, a1 a2 a3 will not mapped in this case:

Here is this example attached, hope it help you:

Regards,

Arun Goel

Re: About $syn

Thu, 05 Apr 2012 11:00:09 GMT

ok arun i dont wnt duplicacy in my case.

Do you know how to join the tables?

Re: About $syn

Fri, 06 Apr 2012 02:56:28 GMT

hi,

just use JOIN keyword between two load statements.

Re: About $syn

Fri, 06 Apr 2012 04:05:44 GMT

You can also use QUALIFY and UNQUALIFY to map selected fields of your choice.

Re: About $syn

Fri, 06 Apr 2012 04:09:38 GMT

Hi Arun,

Thanks for your help.

I tried the qualify but I didn’t get correct result what is syntax I means do we need to add only the command qualify or something else?

Re: About $syn

jagannalla — Fri, 06 Apr 2012 04:39:31 GMT

Qualify *;

UnQualify ID;

Table1:

Load

ID,

A,B,C

From T1.qvd;

UnQualify ID;

Table2:

Load

ID,

A1,B1,C1

From T2.qvd;

UnQualify *;

Re: About $syn

Fri, 06 Apr 2012 04:53:05 GMT

hi,

Suppose in my example, you want to map only field A then:

USE:

QUALIFY *;

UNQUALIFY A;

LOAD * INLINE [

x, A, B, C

1, a1, b1, c1

2, a2, b2, c2

3, a3, b3, c3

4, a4, b4, c4

5, a5, b5, c5

6, a6, b6, c6

];

QUALIFY *;

UNQUALIFY A;

LOAD * INLINE [

x, A, D, E

Co, a1, b1, c1

2, a2, b2, c2

3, a3, b3, c3

7, a7, b7, c7

8, a8, b8, c8

9, a9, b9, c9

];

The result will be:

See the attached file:

Re: About $syn

Fri, 06 Apr 2012 06:50:03 GMT

Ok thanks, I will try it.

Re: About $syn

Fri, 06 Apr 2012 10:14:55 GMT

I have tried qualify but still getting one $syn table

Here I m attaching the syn table table view and script

Can you help me out?

Re: About $syn

CELAMBARASAN — Fri, 06 Apr 2012 10:36:01 GMT

Hi,

Qlikview forms the Sync table if there is a 2 or more fields have common name between two tables,remove the field or rename the field.

Here you have both corporation ID and company id are common in many tables.

have Corporation and company id in any one table and concate of 2 fields with the name key.

all others do a concat both fields like this and remove corporation and company id.

Table1:

Load

Field1 & Field2 as Key,

Field1,

Field2,

Field3....

From...

Table2:

Load

Field1&Field2 as Key,

Field6,

Field7....

From...

Re: About $syn

CELAMBARASAN — Fri, 06 Apr 2012 10:42:02 GMT

Hi,

Check the attached script.

Re: About $syn

Fri, 06 Apr 2012 10:49:10 GMT

So there is no need of qualify?

Re: About $syn

Fri, 06 Apr 2012 10:56:22 GMT

And about the fields which are present in 2/3 tables like thepetic area id?

Re: About $syn

CELAMBARASAN — Fri, 06 Apr 2012 11:05:12 GMT

Hi,

I missed that.Check the edited attachment.

Celambarasan

Re: About $syn

Fri, 06 Apr 2012 11:08:23 GMT

I m not getting the attachment

Re: About $syn

Mon, 09 Apr 2012 07:07:07 GMT

Hi, I have tried the key concept…but not getting correct output…..

Can you help me…..?

Re: About $syn

CELAMBARASAN — Mon, 09 Apr 2012 08:45:00 GMT

Hi,

Use the script from the attached file.

Celambarasan

Re: About $syn

Mon, 09 Apr 2012 08:51:03 GMT

Thank you I m not getting now the $syn table

But if I retain the $syn is there effect on the data?

I mean without $syn table and data with having $syn table in the script?