Question editor for loading data Qlik

[TimSchaeffer]

Hello,

As an assignment me and a other student got the task to create a dashboard for the practice case ''Conimex".

As data we recieved an excel file containing multiple sheets, each providing different sets of information, called ''conimex 24''.

We already worked with this file and we stumble upon serveral bugs we are unable to fix. One of them is getting the correct ''omzet" (turnover). Somehow in the calculation its *100 to many. The "omzet" should be around 7.24M, but somehow we get the result 724M. The same goes for the "marge".

Its an europian dataset, meaning the price and costprice is with an komma instead of a dot. We first tried changing the main code, but this doesn't seem to fix it. Also we tried asking chatGPT, wich gave us the Num function for the calculation, but this calculation doesn't work aswell.

My question is how we can fix the calculation, so it shows the correct amount. In the attachments i will send the excel data we got, aswell as the code we currently have. Each #1 means a different tab in the data load section.

//The edit we tried in main:

SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='€ ###0,00;-€ ###0,00';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY h:mm:ss[.fff] TT';
SET TimeFormat='h:mm:ss TT';

// SET ThousandSep=',';
// SET DecimalSep='.';
// SET MoneyThousandSep=',';
// SET MoneyDecimalSep='.';
// SET MoneyFormat='$ ###0.00;-$ ###0.00';

// SET DateFormat='M/D/YYYY';
// SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

_________________________________________________________________________________________

//The edit we tried in sales from chatGPT:

[InvoiceData]:
NoConcatenate
LOAD
%InvoiceNo,
%CustomerNo_Customer,
%CustomerNo_Invoice,
%ArticleNo,
%InvoiceLine,
%ArtGroupCode,
%SalesmanNo,
%CustomerCategory,
%InvoiceDate,
num#(Quantity, '###0', '.', ',') as Quantity, // Zorg dat Quantity numeriek is
num#(Price, '###0,00', ',', '.') as Price, // Zorg dat Price numeriek is
num#(CostPrice, '###0,00', ',', '.') as CostPrice, // Zorg dat CostPrice numeriek is
num#(Quantity, '###0', '.', ',') * num#(Price, '###0,00', ',', '.') as Omzet, // Correcte berekening Omzet
num#(Quantity, '###0', '.', ',') * num#(CostPrice, '###0,00', ',', '.') as Kosten, // Correcte berekening Kosten
(num#(Quantity, '###0', '.', ',') * num#(Price, '###0,00', ',', '.')) -
(num#(Quantity, '###0', '.', ',') * num#(CostPrice, '###0,00', ',', '.')) as Marge // Correcte berekening Marge
RESIDENT [InvoiceLine];

DROP TABLE [InvoiceLine];

[Or]

I would suggest checking that there isn't something causing multiplication by virtue of an incorrect key or using fields from two tables that aren't joined. The fact that the value you get is exactly 100 times greater than expected suggests something is getting multiplied by 100 (obviously), so check if any of your tables contain exactly that many rows of data as a lead.

[Clement15]

Hello,

After doing a test with excel I think it is better to forget the formats if you do not need them. Generally Qlik manages this well and without I get about 7 millions

[TimSchaeffer]

Hello, thanks for the quick responce!

i checked the code again with my teammate, and i think the issue has to be somewhere with the joins of the invoice in the sales table, this is our current Sales table:

[InvoiceLine]:
LOAD
InvoiceNo as '%InvoiceNo',
InvoiceLine as '%InvoiceLine',
ArticleNo as '%ArticleNo',
Quantity,
Price
FROM [lib://DataFiles/Conimex_24.xlsx]
(ooxml, embedded labels, table is InvoiceLine);

[InvoiceHeader]:
Left Join ([InvoiceLine])
LOAD
InvoiceNo as '%InvoiceNo',
Floor(InvoiceDate) as '%InvoiceDate',
CustomerNo as '%Invoice_CustomerNo'
FROM [lib://DataFiles/Conimex_24.xlsx]
(ooxml, embedded labels, table is InvoiceHeader);

[Article]:
Left Join ([InvoiceLine])
LOAD
ArticleNo as '%ArticleNo',
CostPrice,
ArtGroupCode as '%ArtGroupCode'
FROM [lib://DataFiles/Conimex_24.xlsx]
(ooxml, embedded labels, table is Article);

[Customer]:
Left Join ([InvoiceLine])
LOAD
CustomerNo as '%Customer_CustomerNo',
Category as 'Customer_Category',
SalesmanNo as '%SalesmanNo'
FROM [lib://DataFiles/Conimex_24.xlsx]
(ooxml, embedded labels, table is Customer);

[InvoiceData]:
NoConcatenate
LOAD
%InvoiceNo,
%Customer_CustomerNo,
%Invoice_CustomerNo,
%ArticleNo,
%InvoiceLine,
%ArtGroupCode,
%SalesmanNo,
%InvoiceDate,
Quantity,
CostPrice,
Price,
Quantity * Price as Omzet,
Quantity * CostPrice as Kosten,
(Quantity * Price) - (Quantity * CostPrice) as Marge
RESIDENT [InvoiceLine];

DROP TABLE [InvoiceLine];

//above is a part that loads the customer aswell, the code of the ''CustomerDim'' is:

[Customer]:
LOAD
CustomerNo as '%Customer_CustomerNo',
Name,
City,
Province,
Category as 'Customer_Category'
// SalesmanNo as '%SalesmanNo'

FROM [lib://DataFiles/Conimex_24.xlsx]
(ooxml, embedded labels, table is Customer);

[Clement15]

Hello,

There was a problem with the join of the customer table. There were no fields in common, so no key. This created a Cartesian product. Normally, this code should work:

[InvoiceLine]:
LOAD
InvoiceNo as '%InvoiceNo',
InvoiceLine as '%InvoiceLine',
ArticleNo as '%ArticleNo',
Quantity,
Price
FROM [lib://DataFiles/Conimex_24 (1).xlsx]
(ooxml, embedded labels, table is InvoiceLine);

[InvoiceHeader]:
Left Join ([InvoiceLine])
LOAD
InvoiceNo as '%InvoiceNo',
Floor(InvoiceDate) as '%InvoiceDate',
CustomerNo as '%Invoice_CustomerNo'
FROM [lib://DataFiles/Conimex_24 (1).xlsx]
(ooxml, embedded labels, table is InvoiceHeader);

[Article]:
Left Join ([InvoiceLine])
LOAD
ArticleNo as '%ArticleNo',
CostPrice,
ArtGroupCode as '%ArtGroupCode'
FROM [lib://DataFiles/Conimex_24 (1).xlsx]
(ooxml, embedded labels, table is Article);

// [Customer]:
// Left Join ([InvoiceLine])
// LOAD
// CustomerNo as '%Customer_CustomerNo',
// Category as 'Customer_Category',
// SalesmanNo as '%SalesmanNo'
// FROM [lib://DataFiles/Conimex_24 (1).xlsx]
// (ooxml, embedded labels, table is Customer);

[InvoiceData]:
NoConcatenate
LOAD
%InvoiceNo,
%Invoice_CustomerNo,
%ArticleNo,
%InvoiceLine,
%ArtGroupCode,
%InvoiceDate,
Quantity,
CostPrice,
Price,
Quantity * Price as Omzet,
Quantity * CostPrice as Kosten,
(Quantity * Price) - (Quantity * CostPrice) as Marge
RESIDENT [InvoiceLine];

DROP TABLE [InvoiceLine];

//above is a part that loads the customer aswell, the code of the ''CustomerDim'' is:

[Customer]:
LOAD
CustomerNo as '%Invoice_CustomerNo',
Name,
City,
Province,
Category as 'Customer_Category',
SalesmanNo as '%SalesmanNo'

FROM [lib://DataFiles/Conimex_24 (1).xlsx]
(ooxml, embedded labels, table is Customer);

[TimSchaeffer]

Hello, 

First of all, thank you so much, this seemed to do the trick.

however, after i loaded the data i did get a warning, is there a way to fix the following message, or can i ignore it?

InvoiceHeader << InvoiceHeader

Opgehaalde regels: 15,000

Article-7 << Article Opgehaalde regels: 88

InvoiceData << InvoiceLine

Opgehaalde regels: 83,279

Customer-7 << Customer Opgehaalde regels: 100

$Syn 1 = Name+City+Province+Customer_Category

App is opgeslagen

Voltooid met fout(en) en/of waarschuwing(en)

0 geforceerde fout(en)

1 synthetische sleutel(s)

[Dana_Baldwin]

Hi @TimSchaeffer 

Please note the forum you have posted to is for the Data Integration product Qlik Compose for Data Warehouses. To reach your target audience, please consider posting your question here: https://community.qlik.com/t5/Qlik-Sense/ct-p/qlik-sense

Thanks,

Dana

[Clement15]

Hello,

Theoretically, your model can work, but it is strongly discouraged to have synthetic keys in your model.
Given the fields concerned, you must certainly load another customer table which creates this problem.