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.

Not applicable

Matching two Pivot tables

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!

8 Replies
vgutkovsky
Honored Contributor II

Re: Matching two Pivot tables

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

jmarinesc
New Contributor III

Re: Matching two Pivot tables

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

jmarinesc
New Contributor III

Re: Matching two Pivot tables

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

jmarinesc
New Contributor III

Re: Matching two Pivot tables

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

vgutkovsky
Honored Contributor II

Re: Matching two Pivot tables

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
;

jmarinesc
New Contributor III

Re: Matching two Pivot tables

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

vgutkovsky
Honored Contributor II

Re: Matching two Pivot tables

No problem, please verify correct answers

jmarinesc
New Contributor III

Re: Matching two Pivot tables

Thanks Vlad!

Community Browser