Edit Some data according to some criterio from the script

[BrightFuture]

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.

[BrightFuture]

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?

[rwunderlich · Accepted solution]

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

[BrightFuture]

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.

[BrightFuture]

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

[rwunderlich]

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

[BrightFuture]

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!

[BrightFuture]

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):

Year	Region	Product name	Company	Period	Some other factor	Cost	what the cost should look like	Notes
2000	Au	Blabla1	Blabla2	3	N1	1,000,000	1,000,000
2000	Au	Blabla1	Blabla2	6	N1	2,000,000		delete the whole row after summing with the similar row
2000	Au	Blabla1	Blabla2	6	N2	500,000		delete the whole row after summing with the similar row
2000	Au	Blabla1	Blabla2	6	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	Au	Blabla1	Blabla2	9	N1	3,500,000	2,000,000	Now we use the new row (the sum of the two rows)
2001	US	Bla1	Bla2	3	N1	500	500
2001	US	Bla1	Bla2	6	N1	600	100
2001	US	Bla1	Bla2	9	N1	650	50
2001	UUUUUUUU	Bla1	Bla2	3	N1	50	50
2001	UUUUUUUU	Bla1	Bla2	6	N1	100	50
2001	UUUUUUUU	Bla1	Bla2	9	N1	150	50