Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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.
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.
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?
Are you dropping the table Tabelaa after the resident ?
Thanks & Regards,
Rohan.
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')))))