Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
BrightFuture
Contributor III
Contributor III

Edit Some data according to some criterio from the script

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:

YearRegion

Product name

CompanyPeriod

Cost

what the cost should look like

2000AuBlabla1Blabla231,000,0001,000,000
2000AuBlabla1Blabla262,000,0001,000,000
2000AuBlabla1Blabla293,500,0001,500,000
2001USBla1Bla23500500
2001USBla1Bla26600100
2001USBla1Bla2965050
2001UUUUUUUUBla1Bla235050
2001UUUUUUUUBla1Bla2610050
2001UUUUUUUUBla1Bla2915050

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.

1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

View solution in original post

7 Replies
BrightFuture
Contributor III
Contributor III
Author

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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

YearRegion

Product name

CompanyPeriod

Some other factor

Cost

what the cost should look like

Notes

2000AuBlabla1Blabla23N11,000,0001,000,000
2000AuBlabla1Blabla26N12,000,000delete the whole row after summing with the similar row
2000Au
Blabla1
Blabla2
6N2500,000delete the whole row after summing with the similar row
2000Au
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,000This one now should be instead the last two rows
2000AuBlabla1Blabla29N13,500,0002,000,000Now we use the new row (the sum of the two rows)
2001USBla1Bla23
N1
500500
2001USBla1Bla26
N1
600100
2001USBla1Bla29
N1
65050
2001UUUUUUUUBla1Bla23
N1
5050
2001UUUUUUUUBla1Bla26
N1
10050
2001UUUUUUUUBla1Bla29
N1
15050