topic Re: How can I avoid Sync in this example? in QlikView

How can I avoid Sync in this example?

camilo — Tue, 02 Aug 2011 23:46:23 GMT

Hi, I´m loading two tables but I´m getting syn tables ¿How can I avoid this?

LOAD [Local SAP],

[Id SAP] as ID,

[Departamento SAP] as Departamento,

Month(Dia) as Mes,

Year(Dia) as Año,

Dia as Fecha,

[Unidades Vendidas],

[Venta Neta $]/1000000 as Venta,

[Costo Venta $]/-1000000 as Costo

FROM

(qvd);

LOAD Fecha,

id_local_int as ID,

ID_Categoria,

Venta/1000000 as VentaP,

[Margen dia]/1000000 as MargenP,

Month(Fecha) as Mes,

Year(Dia) as Año,

FROM

(qvd);

Thanks!

Re: How can I avoid Sync in this example?

Wed, 03 Aug 2011 00:16:19 GMT

Could you please upload a sample database for this two table. For the time being, you do not need to write

Month(Fecha) as Mes,

Year(Dia) as Año,

on the [PptoDetalle.qvd] table.

Message was edited by: lalmohan.behera

How can I avoid Sync in this example?

Wed, 03 Aug 2011 01:14:07 GMT

Hi Camilo ,

I think you are getting snc because you are using

[Id SAP] as ID, &

id_local_int as ID both are with same colomn name can you use one as Sap_ID & other one as Local_ID or try to change year alias in any one of the table .

If still you are getting sync tables can you tell me what are the colomn's u r getting in sync table .

thankyou

Meher.

How can I avoid Sync in this example?

Wed, 03 Aug 2011 10:53:10 GMT

Hi, Syn are generated when there are more than one same field names in two tables. Try following code. Hope it will remove syn.

LOAD [Local SAP],

[Id SAP] as ID,

[Departamento SAP] as Departamento,

Month(Dia) as Mes_venta,

Year(Dia) as Año_venta,

Dia as Fecha,

[Unidades Vendidas],

[Venta Neta $]/1000000 as Venta,

[Costo Venta $]/-1000000 as Costo

FROM

(qvd);

LOAD Fecha,

id_local_int as ID,

ID_Categoria,

Venta/1000000 as VentaP,

[Margen dia]/1000000 as MargenP,

Month(Fecha) as Mes,

Year(Dia) as Año,

FROM

(qvd);

How can I avoid Sync in this example?

Wed, 03 Aug 2011 10:59:32 GMT

Hi,

Use below one.

QUALIFY *;

Regards,

Antony.

How can I avoid Sync in this example?

camilo — Wed, 03 Aug 2011 13:22:42 GMT

I marked in bold the sync fields, I need them with the same names in both tables so I can use date or id filters that affects both tables.

If I dont do this when I click on one field such as Dia, I will only see the SAP table data an not the other table data (Fecha), the same happens with the ID field.

Re: How can I avoid Sync in this example?

camilo — Wed, 03 Aug 2011 13:37:03 GMT

Here is a table example:

TABLE 2
Fecha	ID	ID_Categoria	MargenP	Mes	VentaP
40638	L001	1006	0,021054474	4	0,192956297
40638	L001	1007	0,00323144	4	0,013407976
40638	L001	1101	0,010650693	4	0,071221895
40638	L001	1102	0,019352952	4	0,236498163

TABLE 1
Año	Costo Venta $	Departamento SAP	Dia	ID	Local SAP	Mes	Unidades Vendidas	Venta Neta $
2011	-0,000346	ENVASES	40717	L001	CORONEL	6	1	0,000336134
2011	-0,000365	ENVASES	40698	L001	CORONEL	6	1	0,000336134
2011	-0,0048	PILETAS Y PARRILLAS	40721	L001	CORONEL	6	4	0,006354622
2011	-0,000692	ENVASES	40718	L001	CORONEL	6	2	0,00067227
2011	-0,015634	ELECTRO HOGAR	40721	L001	CORONEL	6	2	0,020739496
2011	-0,00039	ENVASES	40712	L001	CORONEL	6	1	0,000336134

Re: How can I avoid Sync in this example?

camilo — Tue, 09 Aug 2011 16:06:29 GMT

In deed I´m getting sinc on those keys (ID), but I need them with the same name to connect the tables. And I need Month, Year in both tables else when I selecct one month or year only the data of one table will be sown (the table wich has the month and year fields on the script).

Re: How can I avoid Sync in this example?

Wed, 10 Aug 2011 03:33:14 GMT

Hi,

Please send an example application.

Re: How can I avoid Sync in this example?

camilo — Fri, 12 Aug 2011 13:39:24 GMT

Hi, I solved the problem concatenating the tables, I don´t know if that the best way but it´s working till now.

Thanks all for your help.

Camilo

How can I avoid Sync in this example?

klausskalts — Fri, 12 Aug 2011 13:53:54 GMT

Concatenation IS a possible solution ... but it's normally better to create a keyfield yourself

Field_no1 & '-' & Field_no_2 as Key

in both tables ....

Re: How can I avoid Sync in this example?

Anonymous — Mon, 03 Sep 2012 04:35:21 GMT

Hi Klaus, If I have more than six table where I need to link all the column to a common key name, so what is good appraoch to avoid the sync key. whether to concatenation or making a common key in all the table, ex: Table1, TB1_Business_Source, as Key Table2, TB2_Business_Source as Key Table3, TB3_Business_Source as Key Table4, TB4_Business_Source as Key Table5, TB5_Business_Source as Key Table6, TB6_Business_Source as Key Need help Best Regards,

Re: How can I avoid Sync in this example?

Thu, 06 Sep 2012 14:47:01 GMT

Hi S Kumar,

I think if you are not ending up with a bunch of null values. give a atry to concatenation or else its always better to go for a composite key.

Its also depends on size of tables which you are having. If concatenation is increasing your tables size drasticlly go for Composite keys.

Hope this helps

thanks

Meher.