topic Data manipulation during load in QlikView

Data manipulation during load

Wed, 25 Nov 2020 16:16:04 GMT

Hi there,

I need to do some data manipulation during the loading procedure and do not quite know how. I have a table such as the below

How can I calculate the Aggregated_Sales entries during the load procedure when given the CustomerId and Sales data?

Thanks a lot for your help,

Florian

marcus_sommer — Sat, 22 Oct 2016 13:44:15 GMT

Maybe in this way:

table1:

load CustomerId, Sales from YourTable;

left join

Load CustomerId, sum(Sales) as Aggregated_Sales resident table1 group by CustomerId;

table2:

load *, Sales / Aggregated_Sales as % resident table1;

drop tables table1;

- Marcus

sunny_talwar — Sat, 22 Oct 2016 14:05:21 GMT

You don't have to do this in the script, you can come up with this on the front end if you want using this expression

Sum(TOTAL <CustomerId> Sales)

but for doing this in the script, you can do like this

Table:

LOAD CustomerId,

Sales

FROM....

Left Join (Table)

LOAD CustomerId,

Sum(Sales) as Aggregated_Sales

Resident Table

Group By CustomerId;

maxgro — Sat, 22 Oct 2016 14:38:04 GMT

you can use the total in the user interface (chart)

for aggregated sales (by customer)

Sum(total <CustomerId> Sales)

and for %

Sum(Sales) / Sum(total <CustomerId> Sales)