Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
In the next code I want to obtain the Price per unit but when I apply the division to these two field the error from below occurs.
[Data]:
LOAD
[ID_Factura],
[Date],
[Market],
[Product],
Sum([Units]) AS [Units],
Sum([Sales]) AS [Sales],
[Sales]/[Units] AS [PricePerUnit],
APPLYMAP( '__countryCodeIsoThree2Polygon', APPLYMAP( '__countryName2IsoThree', LOWER([Market])), '-') AS [Data.Market_GeoInfo]
FROM [lib://Downloads/Data Workshop.xlsx]
(ooxml, embedded labels, table is Data)
WHERE [Date] >= Date#('01/2018') AND NOT IsNull([ID_Factura])
GROUP BY [Date], [Market],[Product],[ID_Factura],[PricePerUnit];
changes highligh6ted below
[Data]:
LOAD
[ID_Factura],
[Date],
[Market],
[Product],
Sum([Units]) AS [Units],
Sum([Sales]) AS [Sales],
Sum([Units]) /Sum([Sales]) AS [PricePerUnit],
APPLYMAP( '__countryCodeIsoThree2Polygon', APPLYMAP( '__countryName2IsoThree', LOWER([Market])), '-') AS [Data.Market_GeoInfo]
FROM [lib://Downloads/Data Workshop.xlsx]
(ooxml, embedded labels, table is Data)
WHERE [Date] >= Date#('01/2018') AND NOT IsNull([ID_Factura])
GROUP BY [Date], [Market],[Product],[ID_Factura];
remove [PricePerUnit] from Group By, this field will exist after the load statement has executed
[Data]:
LOAD
[ID_Factura],
[Date],
[Market],
[Product],
Sum([Units]) AS [Units],
Sum([Sales]) AS [Sales],
[Sales]/[Units] AS [PricePerUnit],
APPLYMAP( '__countryCodeIsoThree2Polygon', APPLYMAP( '__countryName2IsoThree', LOWER([Market])), '-') AS [Data.Market_GeoInfo]
FROM [lib://Downloads/Data Workshop.xlsx]
(ooxml, embedded labels, table is Data)
WHERE [Date] >= Date#('01/2018') AND NOT IsNull([ID_Factura])
GROUP BY [Date], [Market],[Product],[ID_Factura];
Hi, thanks for your response. In these case I obtain the next error.
changes highligh6ted below
[Data]:
LOAD
[ID_Factura],
[Date],
[Market],
[Product],
Sum([Units]) AS [Units],
Sum([Sales]) AS [Sales],
Sum([Units]) /Sum([Sales]) AS [PricePerUnit],
APPLYMAP( '__countryCodeIsoThree2Polygon', APPLYMAP( '__countryName2IsoThree', LOWER([Market])), '-') AS [Data.Market_GeoInfo]
FROM [lib://Downloads/Data Workshop.xlsx]
(ooxml, embedded labels, table is Data)
WHERE [Date] >= Date#('01/2018') AND NOT IsNull([ID_Factura])
GROUP BY [Date], [Market],[Product],[ID_Factura];
Now it works! Thanks