topic Re: Data operations in QlikView

Data operations

Wed, 25 Nov 2020 16:16:04 GMT

Hello,

this is my issue i have this kind of data set :

I would like for each op calculate lthis expression q*price/sale and have this amount as a row of a table.

thanks for your help guys.

regards

Re: Data operations

sunny_talwar — Tue, 17 Jan 2017 12:30:56 GMT

Would you be able to share a sample for us to look at?

Preparing examples for Upload - Reduction and Data Scrambling

Uploading a Sample

Re: Data operations

susovan — Tue, 17 Jan 2017 12:45:27 GMT

Hi,

Try this

Data:

LOAD op,

q ,

price,

sales,

q*price/sales as @_price,

FROM

'Give the path';

Re: Data operations

Tue, 17 Jan 2017 13:26:16 GMT

Hi,

thanks for your answer this is what i wanted to do.

I have one more concerne i try this script but i can't figure out why it does not load the data.

LOAD op,

q ,

q*price/sales as @_price,

price,

sum(price ) as to price,

sales

FROM

Table.xlsx

(ooxml, embedded labels, table is Sheet1);

thanks for your support,

Regards

Re: Data operations

Tue, 17 Jan 2017 15:33:05 GMT

Sorry i would like to say:

LOAD op,

q ,

q*price/sales as @_price,

price,

sum(price ) as sumprice,

sales

FROM

Table.xlsx

(ooxml, embedded labels, table is Sheet1);

i think the proble comme with the sum function on this part.

is there another way that i could use?

thanks for your support

Re: Data operations

eespiritu — Tue, 17 Jan 2017 15:50:29 GMT

Hi,

In the script when you are loading the values from the Excel file, you are applying a sum() function, you should use the Group by statement.

Regards,

Enrique.

Re: Data operations

vishsaggi — Tue, 17 Jan 2017 16:09:41 GMT

May be try this as Enrique suggested:

LOAD op,

q ,

(q*price)/sales as @_price,

price,

sum(price) as [to price],

sales

FROM

Table.xlsx

(ooxml, embedded labels, table is Sheet1)

Group By op,q,price, sales;

Re: Data operations

Mon, 23 Jan 2017 10:51:48 GMT

Hi,

I really thanks you four your support. here is my concern. For each saleop number , i have a lot of sales associated operations. I need to have the sum of the amount of the operations as a entry variable called mont_aff. I want the amount in Montant affaire to be in the field mont_aff. I used the script below as you advised me. it seems like it doesn't perform the addition just the multiplication. would you have any ideas for my concern?

thanks for your support?

Regards,

LOAD

Opération_N° as saleop,

Mnt_Net_client as mnt,

Qté,

sum(Mnt_Net_client*Qté) as [mont_aff]

FROM

(biff, embedded labels, table is [data pmc $]) Group By Opération_N°,Qté, Mnt_Net_client ;

Re: Data operations

vishsaggi — Mon, 23 Jan 2017 14:46:46 GMT

Can you share your sample app and also your expected output you want to see if one single saleop is selected?

Re: Data operations

Mon, 23 Jan 2017 15:13:18 GMT

This screen is exactly what i would like to perform with my datas as in this question : Dynamic variables: Use Concatenated strings as variable in script. i tried the script with my data i can't have the data populated my variable Product contains nothing

Hi,

I am a new user of qlikview. the scrip below does axactly what i would like to do.with my data. i tried it with my data but it is not working. i can't understant why:

Data:

LOAD Opération_N° as Product,

Qté,

Mnt_Net_client as Sale,

// Qté*Mnt_Net_client as [MntM] ,

// Qté*TF as [TFM]

FROM

(biff, embedded labels, table is Feuil2$);

Variable:

LOAD 1 as Temp

AutoGenerate(1);

for i=1 to FieldValueCount('Product')

let vProduct = FieldValue('Product','$(i)');

Concatenate(Variable)

LOAD '$(vProduct)' as PRODUCT,

sum(Sale) as SelectedProduct,

max(Sale) as MaxSale

Resident Data

where Product = '$(vProduct)'

Group by Product;

NEXT i

DROP Field Temp;

Re: Data operations

vishsaggi — Mon, 23 Jan 2017 15:22:10 GMT

I got that, i am not sure why your script is not working. Hence asked for your sample app you are working on. Can you share the above sample app where you have those saleop, tvf,tdv etc fields coming from excel sheet?

Re: Data operations

Mon, 23 Jan 2017 16:05:02 GMT

here you have my working data.

As you can see in the excel sheet the operation in qlik is not working.

Maybe my script has some issue. do you have any ideas?

thanks for your support.

Re: Data operations

vishsaggi — Mon, 23 Jan 2017 16:37:00 GMT

Is this your expected output you are looking for ? Any can i know why you are using that Forloop variable table script ?

Re: Data operations

Mon, 23 Jan 2017 17:01:27 GMT

This is what i was expected exactly.

For me the for loop allows to have the same opperation as an entry field

Re: Data operations

vishsaggi — Mon, 23 Jan 2017 22:58:09 GMT

I didn't get you, what you mean by same operation as an entry field? when you use for loop your data model has two separate tables like iSLAND tables?

Re: Data operations

Tue, 24 Jan 2017 09:08:20 GMT

I mean that i need the data in the table column sum(mnt_affe) to be a filed like a column of my original excel file.

Re: Data operations

vishsaggi — Tue, 24 Jan 2017 15:00:01 GMT

Try this and let me know if this is what you are looking?

Attached test_script.

Re: Data operations

Tue, 24 Jan 2017 15:28:20 GMT

thanks for your support.

With this format , i can't make graphs with mnt_affre as axis. that is why i want mnt_affre as a field. that is why i used the scrpt.

Re: Data operations

vishsaggi — Tue, 24 Jan 2017 15:46:49 GMT

mnt_Affre is coming from the script right? As mentioned in your excel. So when you use a bargraph with Operation as Dim and Sum(Mnt_affre) as your expression it gives you the total sum of Mnt_affre? Is this not you are looking for, can you screen shot of your expected output with dimensions and what you need for expression? Sorry kind of hard to analyze what you are actually looking for?

Re: Data operations

Tue, 24 Jan 2017 15:54:45 GMT

Sorry

I ment sum(mnt_affre) as axis. when i put it in the script it makes an error. i want sum(mnt_affre) as a field, the amount in yellow in the screen shoot below