Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

jmarinesc
New 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

Re: Subtotal Expression after Concatenate,Inner and Load Functs

add at the end something like:

left join load

distinc ESremision,

count(ESremision) as Subtotal

group by ESremision;

jmarinesc
New Contributor III

Re: Subtotal Expression after Concatenate,Inner and Load Functs

Hello Pari Pari;

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

Not applicable

Re: Subtotal Expression after Concatenate,Inner and Load Functs

could you post small qvw sample?

jmarinesc
New Contributor III

Re: Subtotal Expression after Concatenate,Inner and Load Functs

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

Re: Subtotal Expression after Concatenate,Inner and Load Functs

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