topic Re: Edit Some data according to some criterio from the script in QlikView

Edit Some data according to some criterio from the script

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

Hello all,

I am trying to edit some numbers in very large tables and it is doable in chart expressions but it makes it very very slow for the user.

So I looked it up and thought of editing data in the script itself.

What I have is for example:

Year	Region	Product name	Company	Period	Cost	what the cost should look like
2000	Au	Blabla1	Blabla2	3	1,000,000	1,000,000
2000	Au	Blabla1	Blabla2	6	2,000,000	1,000,000
2000	Au	Blabla1	Blabla2	9	3,500,000	1,500,000
2001	US	Bla1	Bla2	3	500	500
2001	US	Bla1	Bla2	6	600	100
2001	US	Bla1	Bla2	9	650	50
2001	UUUUUUUU	Bla1	Bla2	3	50	50
2001	UUUUUUUU	Bla1	Bla2	6	100	50
2001	UUUUUUUU	Bla1	Bla2	9	150	50

The data are not that nice like in this table (they are randomly distributed) but eventually for every product name at a specific year and in a certain region, produced by the same company, will have 3 periods and for each a certain cost.

The problem is that the cost is always given added up to the cost of the last period, so for period 6 the cost should be 1,000,000 instead of 2,000,000. and for Period 9 the cost should be 1,500,000=3,500,000-2,000,000 instead of 3,500,000, etc.

How can I do it in the script? I thought of making:

LOAD

[Year] &'|' & [Region] &'|' & [Product name] &'|' & [Company] & '|'& [Period] as Key

****

and then here needed something to get:

For data with the same Key:

if the key is associated with period=3

leave the cost as it is

else if the key with period=6:

make the cost= Cost_of_period_6_of_this_key - Cost_of_period_3_of_this_key

else if the key with period=9:

make the cost= Cost_of_period_9_of_this_key - Cost_of_period_6_of_this_key

****

FROM

.........

Thank you in advanced for your help.

Re: Edit Some data according to some criterio from the script

BrightFuture — Tue, 19 Jun 2018 11:00:45 GMT

I made some progress but I still have an error because I am trying to use a name that I defined before.

Table1:

LOAD

Cost,

[Year] &'|' & [Region] &'|' & [Product name] &'|' & [Company] & '|'& [Period] as Key

If(Right([Year] &'|' & [Region] &'|' & [Product name] &'|' & [Company] & '|'& [Period],1)=3, Cost) as CostP3,

If(Right([Year] &'|' & [Region] &'|' & [Product name] &'|' & [Company] & '|'& [Period],1)=6, Cost-CostP3) as CostP6,

If(Right([Year] &'|' & [Region] &'|' & [Product name] &'|' & [Company] & '|'& [Period],1)=9,Cost-CostP6 as Cost9,

If(Right([Year] &'|' & [Region] &'|' & [Product name] &'|' & [Company] & '|'& [Period],1)=2, Cost- Cost9) as Cost12

FROM

........................

The problem should be solved if I can use the words in Bold whenever needed. But unfortunately I can not.

Any recommendations what to do?

Re: Edit Some data according to some criterio from the script

rwunderlich — Thu, 19 Jul 2018 22:03:40 GMT

If the data is not already sorted correctly, first load it to a temp table so you can use Order By. Then do:

Final:

NoConcatenate

LOAD

if(Period = 3, Cost, Cost - Previous(Cost)) as Cost,

blah, blah, other fields

Resident TempTable

Order by Year, Region, [Product Name], Company, Period;

Drop Table TempTable;

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Edit Some data according to some criterio from the script

BrightFuture — Thu, 19 Jul 2018 23:23:47 GMT

I am so happy someone finally replied! Thank you
Yes this is actually what I recently did
1) made a script to order the data

2) saved the resulting table as excel file

3) used that table in a new script

and it works but my boss told me that this is not going to work (not accepted) because the whole process is mainly to be done once and no one is going to extract the sorted excel file.

So is there a way to use an aliased variable name? Like the main question was. I thought of LET but did not know how to use it, or maybe you have some suggestion.

Re: Edit Some data according to some criterio from the script

BrightFuture — Thu, 19 Jul 2018 23:33:03 GMT

So mainly when I created the key and Cost3 Cost 6 Cost9 and Cost12 ordering the data happens.

Re: Edit Some data according to some criterio from the script

rwunderlich — Fri, 20 Jul 2018 21:56:41 GMT

You don't need to save the sorted file in an external file. Load the file from the original source and then reread the in-memory copy as I've shown in my example.

Your CostP3/P6/P9 scheme won't work because those fields are on different rows. You would need to JOIN them first, but previous() is probably much easier.

-Rob

Re: Edit Some data according to some criterio from the script

BrightFuture — Tue, 21 Aug 2018 06:17:25 GMT

Thanks a lot Mr. Wunderlich, it took me a while to know how to use this very helpful information, it is just that I did not get them ordered as I wish from the first time so I had to do it as follows:

***First load the original table into a TempTable***

Final:

NoConcatenate

LOAD

if(Period = 3, Cost, Cost - Previous(Cost)) as Cost,

blah, blah, other fields

Resident TempTable

Order by Year, Region, [Product Name], Company, Period;

Drop Table TempTable;

FinalOrdered:

NoConcatenate

LOAD

Cost, Year, Region, [Product Name], Company, Period,

if(Period = 3, Cost, Cost - Previous(Cost)) as Cost,

Resident Final;

Drop Table Final;

and now it works perfectly!

Re: Edit Some data according to some criterio from the script

BrightFuture — Tue, 02 Oct 2018 08:40:13 GMT

Dear Rob,

I still have a problem. I have some rows that should be summed up after ordering and then we can do the subtraction like before. I am not sure if rows summing is possible (keep the resulting row and delete the two). Can you help?

An example of the case as follows with some notes where I should merge the rows (keep all information the same but sum up the cost):

2000

Blabla1

Blabla2

1,000,000

2000

Blabla1

Blabla2

2,000,000

delete the whole row after summing with the similar row

2000

Blabla1

Blabla2

500,000

delete the whole row after summing with the similar row

2000

Blabla1

Blabla2

Does not matter if N1 or N2

(lets say N1 generally)

2,500,000

2,500,000-1,000,000= 1,500,000

This one now should be instead the last two rows

2000

Blabla1

Blabla2

3,500,000

2,000,000

Now we use the new row (the sum of the two rows)

2001

Bla1

Bla2

500

2001

Bla1

Bla2

600

100

2001

Bla1

Bla2

650

2001

UUUUUUUU

Bla1

Bla2

2001

UUUUUUUU

Bla1

Bla2

100

2001

UUUUUUUU

Bla1

Bla2

150