topic Re: How to use functions on the script to get only required rows for a huge data in QlikView

How to use functions on the script to get only required rows for a huge data

Sarath123 — Wed, 25 Nov 2020 16:16:04 GMT

Hello,
I have a sheet of data which has many rows. I need to calculate the sales based on the code provided.

Sales for code1530 is calculated as sales(code(1530)-code(1547)-code(15500140)-code(15500240)-code(15904001)-code(15904002)-code(15904003))

sales for code1547 is calculated as sales(code(1547)-code(15500140)-code(15500240)-code(15904001)-code(15904002)-code(15904003))

sales for code1550 is calculated as sales(code(1550)-code(15500140)+code(15500240))

Sales for code1590 is calculated as sales(code(1590)-code(15904001)+code(50904002)+code(50904003))

I have many other calculations similar to the above

Both the company and total sales should be calculated. I got help on this query previously and when I started executing it for the whole data it is taking 20min to load the full data for the output
I need to show as a single table at the end and
can we use any function on the script which will load only the required code for getting the sales for that code?

Re: How to use functions on the script to get only required rows for a huge data

Arthur_Fong — Thu, 12 Sep 2019 08:26:14 GMT

Try where clause:

load *

resident [YourFilePath]

where match(Code,'A','B','C');

Replace all the codes required in the match function.

Re: How to use functions on the script to get only required rows for a huge data

marcus_sommer — Thu, 12 Sep 2019 12:09:07 GMT

Beside reducing the dataset with an appropriate where-clause like already suggested I think it might be useful to categorize the data within the script. I'm not sure from your description if you have one code-field or multiple ones - by the last you should consider to transform your data with The-Crosstable-Load.

- Marcus

Re: How to use functions on the script to get only required rows for a huge data

Brett_Bleess — Tue, 24 Sep 2019 16:34:11 GMT

Sarath, did Arthur's or Marcus' posts help you with things? If so, be sure to give them credit and let others no what worked by using the Accept as Solution button to mark things. If you did something different, consider posting that and then mark it as the solution. If you are still working on things, leave an update on where things stand.

Regards,
Brett