topic Re: Subtotal Expression after Concatenate,Inner and Load Functs in QlikView

Subtotal Expression after Concatenate,Inner and Load Functs

jmarinesc — Sat, 01 Dec 2012 02:15:10 GMT

Hello QlikCommunity;

I am working with below expressions:

Directory;
TablaA:
LOAD
//,'Table 1' as Table,
    REMISION as remision,
    AEROPUERTO as idestacion,
    LITROS as vnatural,
    CONCEPTO as idproducto,
    Year,
    Month,
    Day
FROM
(qvd);

CONCATENATE

//***************MOVIMIENTO TABLE******************

LOAD
//,'Table 2' as Table,
    idmovimiento,
    tmovimiento,
    mtipo,
    remision,
    idestacion,
    vnatural,
    idproducto,
    Year,
    Month,
    Day
FROM
(qvd);

//******************INNER JOIN*************************
INNER JOIN (TablaA)
LOAD*
WHERE "NoMatch"
OR "SiMatch"
OR "Duplicadas"
OR "DifEstacion"
OR "DifLitros"
OR "IgDesc"
OR "NoMatch1"
OR "NoMatch2"
;
LOAD
remision
,if(count(remision)=1 ,-1)as "NoMatch"
,if(count(remision)>=2,-2)as "SiMatch"
,if(count(remision)>=2,-3) as "Duplicadas"
,if(maxstring(idestacion)<>minstring(idestacion),-4) as "DifEstacion"
,if(max(vnatural)<>min(vnatural),-5)as "DifLitros"
,if(maxstring(idproducto)= minstring(idproducto),-7)as "IgDesc"
,if(count(remision)>=2 and (maxstring(idproducto)=minstring(idproducto)),-1) as "NoMatch1"
,if(count(remision)>=2 and (maxstring(idproducto)<>minstring(idproducto))and (maxstring(tmovimiento)=minstring(tmovimiento))and (maxstring(mtipo)=minstring(mtipo))and (maxstring(idmovimiento)<>minstring(idmovimiento)),-1) as "NoMatch2"

RESIDENT TablaA
GROUP BY remision;

STORE TablaA INTO C:\Users\jmarinesc\Downloads\SIAMCO\01_DATA\SQL\QVD\ESLK1JJOINA.qvd;

Then;

I need to know how many equal ESremision exists making a subtotal on ESNOMATCH2 field and create new field Subtotal, with Subt results (4).

ESremision	ESNOMATCH2	Subtotal
1520689	-1	4
1520689	-1	4
1520689	-1	4
1520689	-1	4

Like An Excel Subt Function.........

Remision	Tot
1	1
Cuenta 1	1
222	1
222	1
Cuenta 222	2
333	1
333	1
333	1
Cuenta 333	3
4444	1
4444	1
4444	1
4444	1
Cuenta 4444	4
55555	1
55555	1
55555	1
55555	1
55555	1
Cuenta 55555	5
Cuenta general	15

Tk in advanced

JMC

Re: Subtotal Expression after Concatenate,Inner and Load Functs

Sat, 01 Dec 2012 15:13:07 GMT

add at the end something like:

left join load

distinc ESremision,

count(ESremision) as Subtotal

group by ESremision;

Re: Subtotal Expression after Concatenate,Inner and Load Functs

jmarinesc — Mon, 03 Dec 2012 19:18:44 GMT

Hello Pari Pari;

It works partially, because I lost my concatenated table.....

Re: Subtotal Expression after Concatenate,Inner and Load Functs

Tue, 04 Dec 2012 07:28:21 GMT

could you post small qvw sample?

Re: Subtotal Expression after Concatenate,Inner and Load Functs

jmarinesc — Tue, 04 Dec 2012 15:31:53 GMT

Hello!

I don´t get error.....

the result of this is:

TablaA << PedidosVentas1Jun2010 (qvd optimized) 20,292 registros leídos
TablaA << Movs1Jun2010 (qvd optimized) 46,066 registros leídos
TablaA 26,031 registros leídos

--- Ejecución de Script finalizada ---

But, I don´t find Subtotal field......

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;-$#,##0.00';
SET TimeFormat='hh:mm:ss TT';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff] TT';
SET MonthNames='ene;feb;mar;abr;may;jun;jul;ago;sep;oct;nov;dic';
SET DayNames='lun;mar;mié;jue;vie;sáb;dom';

Directory;
TablaA:
LOAD

    REMISION as remision,
    AEROPUERTO as idestacion,
    LITROS as vnatural,
    CONCEPTO as idproducto,
    Year,
    Month,
    Day
FROM
(qvd);

CONCATENATE

//***************MOVIMIENTO TABLE******************

LOAD
    idmovimiento,
    tmovimiento,
    mtipo,
    remision,
    idestacion,
    vnatural,
    idproducto,
    Year,
    Month,
    Day
FROM
(qvd);

//******************INNER JOIN*************************
INNER JOIN (TablaA)
LOAD*
WHERE "NoMatch"
OR "SiMatch"
OR "Dup"
OR "Trip"
OR "Cuad"
OR "Quint"
OR ">5"
OR "DifEstacion"
OR "DifLitros"
OR "IgDesc"
OR "NoMatch1"
OR "NoMatch2"
;
LOAD
remision
,if(count(remision)=1 ,-1)as "NoMatch"
,if(count(remision)>=2,-2)as "SiMatch"
,if(maxstring(idestacion)<>minstring(idestacion),-4) as "DifEstacion"
,if(max(vnatural)<>min(vnatural),-5)as "DifLitros"
,if(maxstring(idproducto)= minstring(idproducto),-7)as "IgDesc"
,if(count(remision)>=2 and (maxstring(idproducto)=minstring(idproducto)),-1) as "NoMatch1"
,if(count(remision)>=2 and (count(distinct tmovimiento))=1 and (sum(idproducto))<=10,-1)as "NoMatch2"
,if(count(remision)=2,-12) as "Dup"
,if(count(remision)=3,-13) as "Trip"
,if(count(remision)=4,-14) as "Cuad"
,if(count(remision)=5,-15) as "Quint"
,if(count(remision)>5,-16) as ">5"

RESIDENT TablaA
group by remision;

left join LOAD
distinct remision,

count(remision)as Subtotal

group by remision;

STORE TablaA INTO C:\Users\jmarinesc\Downloads\SIAMCO\01_DATA\SQL\QVD\ESLK1JJOINA.qvd;

Tk in advanced!

Re: Subtotal Expression after Concatenate,Inner and Load Functs

Tue, 04 Dec 2012 18:31:43 GMT

post small piece of Movs1Jun2010.qvd and PedidosVentas1Jun2010.qvd please