Skip to main content
Announcements
Customer Spotlight: Discover what’s possible with embedded analytics Oct. 16 at 10:00 AM ET: REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jmarinesc
Contributor III
Contributor III

Subtotal Expression after Concatenate,Inner and Load Functs

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).

ESremisionESNOMATCH2Subtotal
1520689-14
1520689-14
1520689-14
1520689-14

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

RemisionTot
11
Cuenta 11
2221
2221
Cuenta 2222
3331
3331
3331
Cuenta 3333
44441
44441
44441
44441
Cuenta 44444
555551
555551
555551
555551
555551
Cuenta 555555
Cuenta general15

Tk in advanced

JMC

5 Replies
Not applicable

add at the end something like:

left join load

distinc ESremision,

count(ESremision) as Subtotal

group by ESremision;

jmarinesc
Contributor III
Contributor III
Author

Hello Pari Pari;

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

Not applicable

could you post small qvw sample?

jmarinesc
Contributor III
Contributor III
Author

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!

Not applicable

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