topic Matching two Pivot tables in QlikView

Matching two Pivot tables

Thu, 15 Nov 2012 16:05:43 GMT

Hello Qlik Community;

I have 2 Large Pivot Tables with One Key field (remision) on both, then I need to match these Tables and prompt the results with match or unmatching results.......Is it possible? .......

Thanks a lot!

Re: Matching two Pivot tables

vgutkovsky — Thu, 15 Nov 2012 20:44:20 GMT

Unfortunately that's not possible. You will need to copy the expression(s) from one table to the other one and use it/them to compare the results.

Regards,

Vlad

Re: Matching two Pivot tables

jmarinesc — Thu, 15 Nov 2012 22:47:27 GMT

Hi; I received below message trying to match one field:

Field names must be unique in concatenate table...Load ,'Table2' as Table,

remision,idestacion,vnatural

My script:

Directory;

TablaA:

,'Table 1' as Table,

REMISION as remision,

AEROPUERTO as idestacion,

LITROS as vnatural

FROM

(qvd);

INNER JOIN (Table)

LOAD*

WHERE "Only in one table?"

OR "idestacion Different?"
OR "vnatural Different?"
;
LOAD

remision

,if (count (remision) <2,-1) as "Only in one Table?"

,if(maxstring(idestacion)<>minstring(idestacion),-1) as "idestacion Different?"
,if(max(vnatural)<>min(vnatural),-1) as "vnatural Different?"
RESIDENT Table
GROUP BY remision;

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

LOAD*
,'Table 2' as Table,
remision,
idestacion,
vnatural
FROM
(qvd);

CONCATENATE

Re: Matching two Pivot tables

jmarinesc — Thu, 15 Nov 2012 22:47:40 GMT

Hi; I received below message trying to match one field:

Field names must be unique in concatenate table...Load ,'Table2' as Table,

remision,idestacion,vnatural

My script:

Directory;

TablaA:

,'Table 1' as Table,

REMISION as remision,

AEROPUERTO as idestacion,

LITROS as vnatural

FROM

(qvd);

INNER JOIN (Table)

LOAD*

WHERE "Only in one table?"

OR "idestacion Different?"
OR "vnatural Different?"
;
LOAD

remision

,if (count (remision) <2,-1) as "Only in one Table?"

,if(maxstring(idestacion)<>minstring(idestacion),-1) as "idestacion Different?"
,if(max(vnatural)<>min(vnatural),-1) as "vnatural Different?"
RESIDENT Table
GROUP BY remision;

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

LOAD*
,'Table 2' as Table,
remision,
idestacion,
vnatural
FROM
(qvd);

CONCATENATE

Re: Matching two Pivot tables

jmarinesc — Thu, 15 Nov 2012 22:48:32 GMT

Hi; I received below message trying to match one field:

Field names must be unique in concatenate table...Load ,'Table2' as Table,

remision,idestacion,vnatural

My script:

Directory;

TablaA:

,'Table 1' as Table,

REMISION as remision,

AEROPUERTO as idestacion,

LITROS as vnatural

FROM

(qvd);

INNER JOIN (Table)

LOAD*

WHERE "Only in one table?"

OR "idestacion Different?"
OR "vnatural Different?"
;
LOAD

remision

,if (count (remision) <2,-1) as "Only in one Table?"

,if(maxstring(idestacion)<>minstring(idestacion),-1) as "idestacion Different?"
,if(max(vnatural)<>min(vnatural),-1) as "vnatural Different?"
RESIDENT Table
GROUP BY remision;

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

LOAD*
,'Table 2' as Table,
remision,
idestacion,
vnatural
FROM
(qvd);

CONCATENATE

Re: Matching two Pivot tables

vgutkovsky — Fri, 16 Nov 2012 13:41:01 GMT

James,

I think I misunderstood you. I thought that by "pivot table" you meant a QV pivot table, not a pivot table in a data source. Something like this could probably work for you:

TablaA:
LOAD
   'Table 1' as Table,
   REMISION as remision,
   AEROPUERTO as idestacion,
   LITROS as vnatural
FROM
 (qvd); 

CONCATENATE LOAD
   'Table 1' as Table,
   REMISION as remision,
   AEROPUERTO as idestacion,
   LITROS as vnatural
FROM
 (qvd); 
 
LEFT JOIN LOAD
   *,
   if (count (remision) <2,
      'Only in one Table?',
      if(maxstring(idestacion)<>minstring(idestacion),
         'idestacion Different?',
         if(max(vnatural)<>min(vnatural)
            'vnatural Different?',
            'Match'
         )
      )
   ) as Comparison
RESIDENT TablaA
GROUP BY remision
;

Re: Matching two Pivot tables

jmarinesc — Fri, 16 Nov 2012 23:45:51 GMT

Hello;

Thanks a lot! Below my last Script. It works.

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
//,'Table 1' as Table,
    REMISION as remision,
    AEROPUERTO as idestacion,
    LITROS as vnatural,
    Year,
    Month,
    Day
FROM
(qvd);

CONCATENATE

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

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

//******************INNER JOIN*************************
INNER JOIN (TablaA)
LOAD*
WHERE "No Match Tmov=S"
OR "No Match Tmov =E,M"
OR "Si Match idestacion Different"
OR "vnatural Different?"
;
LOAD
remision
,if(count(remision)<2 ,-1) as "No Match Tmov=S"
,if(count(remision)<2 ,-2) as "No Match Tmov =E,M"
,if(maxstring(idestacion)<>minstring(idestacion),-3) as "Si Match idestacion Different"
,if(max(vnatural)<>min(vnatural),-4) as "vnatural Different?"
RESIDENT TablaA
GROUP BY remision;

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

Best Regards

Jaime

Re: Matching two Pivot tables

vgutkovsky — Sat, 17 Nov 2012 15:31:14 GMT

No problem, please verify correct answers

Re: Matching two Pivot tables

jmarinesc — Wed, 28 Nov 2012 19:33:51 GMT

Thanks Vlad!