HELP: Data multiplied by a fixed factor out of thin air

[Ratier]

I have this excel workbook that I use as data source for a crosstable at Qlik Sense. My workbook had, until now, data from Jan to Mar 2023 and everything was fine.

The problem started when I wrote data from April 2023 to the Workbook and then uploaded it to Qlik Sense. My script in the "data load editor" tab started to calculate every number 130 times bigger than it actually was. For instance, I had:

Month	sum(Valores)
Jan	10
Feb	15
Mar	20
SUM	45

and was expecting as I uploaded April data:

Month	sum(Valores)
Jan	10
Feb	15
Mar	20
Apr	25
SUM	70

Instead I got:

Month	sum(Valores)
Jan	1300
Feb	1950
Mar	2600
Apr	3250
SUM	9100

I didn't change anything in the script and have never seen such problem. Also, in the last month, for exemple, when I had Jan and Feb data and was updating it with the data from March 2023, nothing strange happened.

The question: what happened and how to fix it?

OBS: Here's a sample of the crosstable script. The second part is used to create a drop down group (Reasons --> Causas) as a Dimension and "Sum(Values)" as Measure in a Pie Chart:

Tabelaa:

crosstable (Causas, Valores,7)

LOAD

    "Name",

    "Code",

    Incoterm,

    "Client",

    "Month",

    "Year",

    "Main Product",

    “Causa1”,

    “Causa2”,

    “Causa3”,

    “Causa4”,

    “Causa5”,

    “Causa6”,

    “Causa7”,

    “Causa8”,

    “Causa9”,

    “Causa10”,

    “Causa11”,

    “Causa12”,

    “Causa13”,

    “Causa14”,

    “Causa15”,

    “Causa16”,

    “Causa17”,

    “Causa18”,

    “Causa19”

FROM [lib://AttachedFiles/EXP_QLIK.xlsx]

(ooxml, embedded labels, table is EXP_QLIK);

LOAD

*,

If(Match(Causas, 'Causa1','Causa2','Causa3','Causa4','Causa5')>0, 'A', If(Match(Causas, 'Causa6','Causa7')>0, 'B', If(Match(Causas, 'Causa8','Causa9')>0, 'C', If(Match(Causas, 'Causa10','Causa11','Causa12','Causa13','Causa14','Causa15')>0, 'D', If(Match(Causas, 'Causa16','Causa17')>0, 'E', 'F'))))) as "Reasons"

Resident Tabelaa;

[Rohan · Accepted solution]

Hi,

Have you tried running only this cross-table part seperately & checked if the values are correct or inflated.

If the values are correct there, then check the rest of the script for any incorrect left join or some other error in the code.

Thanks & Regards,

Rohan. 

[Ratier]

I have done that and if I use only the cross-table part, everything begins to work just fine. The problem is that I need the second part.

I removed this part:

LOAD

*,

If(Match(Causas, 'Causa1','Causa2','Causa3','Causa4','Causa5')>0, 'A', If(Match(Causas, 'Causa6','Causa7')>0, 'B', If(Match(Causas, 'Causa8','Causa9')>0, 'C', If(Match(Causas, 'Causa10','Causa11','Causa12','Causa13','Causa14','Causa15')>0, 'D', If(Match(Causas, 'Causa16','Causa17')>0, 'E', 'F'))))) as "Reasons"

Resident Tabelaa;

Also tried to put it back again but in a new section. The problem persisted. How do I put that back in without multiplying it by 130?

[Rohan]

Are you dropping the table Tabelaa after the resident ?

Thanks & Regards,

Rohan.

[Ratier]

No I was not, but I was able to resolve the Issue by creating a master item with the formula bellow and include it in my drop down group. This way I solved the problem by removing from the script the part that was (I don't know why) multiplying everything by 130.

If(Match(Causas, 'Causa1','Causa2','Causa3','Causa4','Causa5')>0, 'A', If(Match(Causas, 'Causa6','Causa7')>0, 'B', If(Match(Causas, 'Causa8','Causa9')>0, 'C', If(Match(Causas, 'Causa10','Causa11','Causa12','Causa13','Causa14','Causa15')>0, 'D', If(Match(Causas, 'Causa16','Causa17')>0, 'E', 'F')))))