topic Pivot Double the values compare with table in QlikView

Pivot Double the values compare with table

Robert78 — Sat, 16 Nov 2024 19:01:19 GMT

hi guys,

I am facing a problem regarding a pivot table.

I am connected to a big data table. And I have some selection.

The problem is the difference between pivot table which shows 16 as Sum(Valoare) but the straight table show only two records, 8 + 0 =8.

The table shows the correct values, but why pivot table doubles the values ?

The same situation is for any date selected.

please advice 🙂

Re: Pivot Double the values compare with table

deepakahirwar — Fri, 28 Feb 2020 14:42:44 GMT

Hello Dear,

It may happen if you're joining tables with one-to-many relationships into one logical table in QV.

Try using aggr() in your pivot table

It maybe something like this:
aggr(<your current expression>, <value>)

Regards,

Deepak

Re: Pivot Double the values compare with table

marcus_sommer — Fri, 28 Feb 2020 15:24:56 GMT

Each object in Qlik shows only distinct values respectively the distinct combination of values. To monitor the data like they are really loaded you need to add an unique id to your tablebox - if none exists you could create one in the script by adding recno() and/or rowno() to the load-statement of these data.

- Marcus

Re: Pivot Double the values compare with table

Robert78 — Tue, 03 Mar 2020 11:21:09 GMT

Hi guys,

I am not using any join, I just load a simple qvd with this script (see below):

I cannot undestand why all values are doubled in pivot, while in straight table all values are correct. I have displayed all columns !

SET vCaleDate=C:\Users\bro-rio\Desktop\Proiecte Baumit\QLICKVIEW\Raport BRO QLICK\;

LOAD
[ADM],
[AnFactura],
[An],
[BAU_OBJECTIVEID],
[CANTITATE_HC],
[CANTITATE_LEMN],
[CANTITATE_METAL],
[CANTITATE_POLIETILENA],
[CANTITATE_PP],
[COD_GRUPA_2017],
[COD_GRUPA_PRINCIPALA_2017],
[Cant_Tot],
[CantitateFacturata],
[CantitateTotalaFacturata],
[Cantitate],
[CodArticol],
[CodClientFacturare],
[CodGrupaPrincipala],
[CodGrupa],
[CodSubGrupa],
[CodclientFiliala],
[Consum],
[Cost_Total],
[Cost_Unitar],
[DataEfectivaALivrarii],
[DataFactura],
If(Len(Trim(DataFactura)), DataFactura, 'NoDate') as DataFactura_ok, // aici inlocuiesc NULL cu "NoDate",
[DataLivrare],
[Data],
[DescriereArticol],
[Expr1],
[Expr2],
[FACTOR],
[FINALSALESRESPONSIBLE],
[GROSIME_POLISTIREN],
[GRUPA_2017],
[GrupaPrincipala],
[Grupa],
[IdFactura],
[LINEDISC],
[LINENUM],
[LINEPERCENT],
[LunaFactura],
[Luna],
[M3_POLISTIREN],
[M3_POLISTIREN_Facturat],
[NrComanda],
[Nr_Siloz],
[NumeArticol],
[NumeClientPrincipal],
[NumeFiliala],
[ReferintaClient],
[SALESPRICE],
[SUPRAFATA_DIN_CONSUM],
[Site],
[Status],
[SubGrupa],
[TRANSTABLEID],
[TipClient],
[Transport],
[UM],
[ValoareDiscountSuplimentarFacturat],
[ValoareDiscountSuplimentar],
[ValoareFacturata],
[Valoare],
[Zona]

FROM
[$(vCaleDate)XX_BRO_CA_ONLINE.qvd]
(qvd);

Re: Pivot Double the values compare with table

Robert78 — Tue, 03 Mar 2020 14:30:32 GMT

Hi there,

I think i solve it. The problem was my qvd file, somehow all the records was doubled.

I recreate the qvd and now is perfect !! 🙂

case closed.