topic Re: left join of two QVDS in QlikView

left join of two QVDS

gr_eldred — Fri, 03 Nov 2017 01:46:23 GMT

Is there an error in this syntax? I need to make a left join between these two qvds.

Let vVtaSemanaDía = date(today(), 'YYYYMMDD');

tmp_vta_6W$(vVtaSemanaDía):

Load

%Pto_vta_ID,

Clasificación,

Cant_Estandar_Act6W

FROM

Transformación\Fact\tmp_Vta_Act_6W.qvd (qvd);

Left Join

LOAD

%Pto_vta_ID,

Clasificación,

Cant_Estandar_Ant6W

FROM

Transformación\Fact\tmp_Vta_Ant_6W.qvd (qvd);

Can you help me?

Re: left join of two QVDS

vishsaggi — Fri, 03 Nov 2017 01:57:52 GMT

May be you want to try like below:

tmp_vta_6W$(=vVtaSemanaDía):

Load

%Pto_vta_ID,

Clasificación,

Cant_Estandar_Act6W

FROM

Transformación\Fact\tmp_Vta_Act_6W.qvd (qvd);

Left Join

LOAD

%Pto_vta_ID,

Clasificación,

Cant_Estandar_Ant6W

FROM

Transformación\Fact\tmp_Vta_Ant_6W.qvd (qvd);

However, just curious you know right if you do left join it will load all values from Left table and only matching values from right table. IF there are no matches it will not load.

May be you would want to do a concatenate as the field names are same.

Re: left join of two QVDS

Anil_Babu_Samineni — Fri, 03 Nov 2017 03:32:59 GMT

Why are you going to have Left Join, Because i don't think you need Joins here due to single table..

Re: left join of two QVDS

avinashelite — Fri, 03 Nov 2017 05:45:38 GMT

I don't see any error in the syntax ....are you facing any issue ?

Re: left join of two QVDS

gr_eldred — Fri, 03 Nov 2017 15:29:35 GMT

yes, I have a problem with this syntax I need to create a qvdde this table, but I can not generate it because it has this variable, can you help me?

Re: left join of two QVDS

vishsaggi — Fri, 03 Nov 2017 15:45:40 GMT

Did you try the one i mentioned in my first msg? If yes what is happening? Any error again or please let me know if have missed anything.

Re: left join of two QVDS

gr_eldred — Fri, 03 Nov 2017 15:57:12 GMT

if I already probe it and it works well my problem is like generating a qvd with the name of the table that brings the variable

Re: left join of two QVDS

vishsaggi — Fri, 03 Nov 2017 16:08:47 GMT

Can you share the script and highlight where exactly you are having problems with? An error snapshot would help?

Re: left join of two QVDS

Anonymous — Fri, 03 Nov 2017 16:20:33 GMT

Are you getting an error message? What is it? A left join on the same table doesn't make sense.

Addressing the variable name directly you could try a static table name and then rename it.:

Let vVtaSemanaDía = 'tmp_vta_6W' & date(today(), 'YYYYMMDD');

tmp_vta_6W:

Load

%Pto_vta_ID,

Clasificación,

Cant_Estandar_Act6W

FROM

Transformación\Fact\tmp_Vta_Act_6W.qvd (qvd);

Left Join(tmp_vta_6W)

LOAD

%Pto_vta_ID,

Clasificación,

Cant_Estandar_Ant6W

FROM

Transformación\Fact\tmp_Vta_Ant_6W.qvd (qvd);

Rename Table tmp_vta_6W to $(vVtaSemanaDía);

Re: left join of two QVDS

gr_eldred — Fri, 03 Nov 2017 16:32:51 GMT

I need to create a qvd with this table name

is it right to do this?

tmp_vta_6W$(=vVtaSemanaDía):

Load

%Pto_vta_ID,

Clasificación,

Cant_Estandar_Act6W

FROM

Transformación\Fact\tmp_Vta_Act_6W.qvd (qvd);

Left Join

LOAD

%Pto_vta_ID,

Clasificación,

Cant_Estandar_Ant6W

FROM

Transformación\Fact\tmp_Vta_Ant_6W.qvd (qvd);

Store tmp_vta_6W$(vVtaSemanaDía) into [..\1_QVDs\Transformación\Fact\Temp\tmp_vta_6W$(vVtaSemanaDía).qvd](qvd);

Re: left join of two QVDS

Anonymous — Fri, 03 Nov 2017 16:39:26 GMT

If all you REALLY need is to create a qvd with that name, it doesn't really matter what name is in your datamodel table.

Something like:

tmp_vta_6W:

Load

%Pto_vta_ID,

Clasificación,

Cant_Estandar_Act6W

FROM

Transformación\Fact\tmp_Vta_Act_6W.qvd (qvd);

Left Join(tmp_vta_6W)

LOAD

%Pto_vta_ID,

Clasificación,

Cant_Estandar_Ant6W

FROM

Transformación\Fact\tmp_Vta_Ant_6W.qvd (qvd);

let vQVDName='[..\1_QVDs\Transformación\Fact\Temp\tmp_vta_6W' & $(vVtaSemanaDía) & '.qvd]'

Store tmp_vta_6W into $(QVDName)(qvd);

Re: left join of two QVDS

maxgro — Fri, 03 Nov 2017 17:45:04 GMT

Let vVtaSemanaDía = date(today(), 'YYYYMMDD');

tmp_vta_6W$(vVtaSemanaDía):

Load

......

Left Join

......

STORE [tmp_vta_6W$(vVtaSemanaDía)] into [tmp_vta_6W$(vVtaSemanaDía).qvd] (qvd);

The syntax is correct

The Qvd is created

What's the problem?

Re: left join of two QVDS

gr_eldred — Fri, 03 Nov 2017 20:16:08 GMT

hello again I just tried but still sending error in the flow does not find the QVD because I do not believe it.

tmp_vta_6W:

Load

%Pto_vta_ID,

Clasificación,

Cant_Estandar_Act6W

FROM

Transformación\Fact\tmp_Vta_Act_6W.qvd (qvd);

Left Join

LOAD

%Pto_vta_ID,

Clasificación,

Cant_Estandar_Ant6W

FROM

Transformación\Fact\tmp_Vta_Ant_6W.qvd (qvd);

let vQVDName='[..\1_QVDs\Transformación\Fact\Temp\tmp_vta_6W' & $(vVtaSemanaDía) & '.qvd]';

Store tmp_vta_6W into $(QVDName)(qvd);

Re: left join of two QVDS

gr_eldred — Fri, 03 Nov 2017 21:37:42 GMT

can anybody help me?

Re: left join of two QVDS

Anonymous — Fri, 03 Nov 2017 21:43:14 GMT

So you're getting an error that

Transformación\Fact\tmp_Vta_Act_6W.qvd (qvd)

Does not exist?

Re: left join of two QVDS

gr_eldred — Fri, 03 Nov 2017 21:51:12 GMT

if you do not create the qvd I need you to create the qvd of this table relationship

Re: left join of two QVDS

Anonymous — Fri, 03 Nov 2017 21:56:01 GMT

You're referring to the QVD in your script in all your posts. If it doesn't exist yet, where are you inputting data from?

Re: left join of two QVDS

gr_eldred — Fri, 03 Nov 2017 22:04:46 GMT

yes, I need to generate a qvd of the relationship of these two tables and this same qvd use for the next flow, at the time of execution sends me an error that says: that the table does not exist.

Let vVtaSemanaDía = date(today(), 'YYYYMMDD');

tmp_vta_6W$(vVtaSemanaDía):

Load

%Pto_vta_ID,

Clasificación,

Cant_Estandar_Act6W

FROM

Transformación\Fact\tmp_Vta_Act_6W.qvd (qvd);

Left Join

LOAD

%Pto_vta_ID,

Clasificación,

Cant_Estandar_Ant6W

FROM

Transformación\Fact\tmp_Vta_Ant_6W.qvd (qvd);

Store tmp_vta_6W$(vVtaSemanaDía) into [..\1_QVDs\Transformación\Fact\Temp\tmp_vta_6W$(vVtaSemanaDía).qvd](qvd);

Re: left join of two QVDS

Anonymous — Fri, 03 Nov 2017 22:11:36 GMT

You say, "relationship of these two tables and this same qvd". What tables and what qvd? From your script, you're using 1 qvd to create 2 tables with the same data and joining them together. And then creating another qvd with a different name.

Where is your source data? What are you looking at to know that your column names are :

%Pto_vta_ID,

Clasificación,

Cant_Estandar_Ant6W

if the qvd doesn't exist yet?

Re: left join of two QVDS

gr_eldred — Fri, 03 Nov 2017 22:53:20 GMT

This is the complete code.

I create a sales qvd with current year-- tmp_Vta_Act_6W:

I created a last year sales qvd-- tmp_Vta_Ant_6W:

I use a left join to cross these two qvds. The name of this Qvd needs to have a variable that brings the current date. --- tmp_vta_6W $ (vVtaSemanaDía):

(This is the Qvd that I can not generate)

The goal is to use this QVD tmp_vta_6W $ (vVtaSemanaDía):

to do the division

Cant_Estandar_Act6W / Cant_Estandar_Ant6W as Cant6W

and re-generate a Qvd with this operation.

of name --Vta_6W:

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

Sub Master_Venta

Let MyMessage = ' ========================= Fact =========================';

trace $(MyMessage);

Let vVtaSemanaDía = date(today(), 'YYYYMMDD');

MaxTable:

LOAD

Max(SemamaAñoApple_Numerado) as MaxValue

FROM

Transformación\Dim\CalendarioApple.qvd (qvd);

Let vMaxValue = Peek('MaxValue',0,'MaxTable');

//Año Actual

tmp_Vta_Act_6W:

LOAD %Pto_vta_ID,

ApplyMap('Map_Clasific_art', %Art_ID,'N/A') as Clasificación,

sum(Cantidad_Estandar) as Cant_Estandar_Act6W

FROM

Transformación\Fact\Temp\tmp_ventaspico_Test.qvd (qvd)

where

ApplyMap('Map_Calendario_Sem', Fecha,0)<= $(vMaxValue)-1

and ApplyMap('Map_Calendario_Sem', Fecha,0)>= $(vMaxValue)-7

and WildMatch( ApplyMap('Map_Clasific_art', %Art_ID,'N/A'), 'Mac', 'Ipad', 'Iphone', 'Apple Watch', 'Accesorios Apple')

Group by %Pto_vta_ID,

ApplyMap('Map_Clasific_art', %Art_ID,'N/A')

;

Store tmp_Vta_Act_6W into [..\1_QVDs\Transformación\Fact\Temp\tmp_Vta_Act_6W.qvd](qvd);

//Año Anterior

tmp_Vta_Ant_6W:

LOAD %Pto_vta_ID,

ApplyMap('Map_Clasific_art', %Art_ID,'N/A') as Clasificación,

sum(Cantidad_Estandar) as Cant_Estandar_Ant6W

FROM

Transformación\Fact\Temp\tmp_ventaspico_Test.qvd (qvd)

where

ApplyMap('Map_Calendario_Sem', Fecha,0)< $(vMaxValue)-53

and ApplyMap('Map_Calendario_Sem', Fecha,0)>= $(vMaxValue)-59

and WildMatch( ApplyMap('Map_Clasific_art', %Art_ID,'N/A'), 'Mac', 'Ipad', 'Iphone', 'Apple Watch', 'Accesorios Apple')

Group by %Pto_vta_ID,

ApplyMap('Map_Clasific_art', %Art_ID,'N/A')

;

Store tmp_Vta_Ant_6W into [..\1_QVDs\Transformación\Fact\Temp\tmp_Vta_Ant_6W.qvd](qvd);

//calculo 6 W

tmp_vta_6W$(vVtaSemanaDía):

Load

%Pto_vta_ID,

Clasificación,

Cant_Estandar_Act6W

FROM

Transformación\Fact\tmp_Vta_Act_6W.qvd (qvd);

Concatenate (tmp_vta_6W$(vVtaSemanaDía))

LOAD

%Pto_vta_ID,

Clasificación,

Cant_Estandar_Ant6W

FROM

Transformación\Fact\tmp_Vta_Ant_6W.qvd (qvd);

Store tmp_vta_6W$(vVtaSemanaDía) into [..\1_QVDs\Transformación\Fact\Temp\tmp_vta_6W$(vVtaSemanaDía).qvd](qvd);

Vta_6W:

Load

%Pto_vta_ID,

Clasificación,

date(Fecha_Acum) as Fecha_Fin_Semana,

Cant_Estandar_Act6W/Cant_Estandar_Ant6W as Cant6W

FROM

Transformación\Fact\tmp_vta_6W$(vVtaSemanaDía).qvd (qvd);

Store Vta_6W into [..\1_QVDs\Transformación\Fact\Temp\Vta_6W.qvd](qvd);

end SUB