Subtotal Expression after Concatenate,Inner and Load Functs

[jmarinesc]

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

[#2]

add at the end something like:

left join load

distinc ESremision,

count(ESremision) as Subtotal

group by ESremision;

[jmarinesc]

Hello Pari Pari;

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

[#4]

could you post small qvw sample?

[jmarinesc]

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!

[#6]

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