Hi all,
I have the following task:
There is one big dataset which is unordened and contains data for all PRODUCTS:
| PRICE |
PRODUCT |
X |
Y |
Z |
| 10 |
1 |
2 |
6 |
8 |
| 13 |
1 |
13 |
0 |
15 |
| 15 |
2 |
12 |
9 |
5 |
| 16 |
6 |
4 |
10 |
11 |
| 12 |
7 |
12 |
10 |
14 |
| 11 |
5 |
0 |
4 |
12 |
| 10 |
3 |
13 |
1 |
4 |
| 12 |
1 |
0 |
15 |
13 |
| 13 |
2 |
11 |
2 |
10 |
I need separate tables for each product and it needs to be ordered in a certain way.
So I thought this would work :
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
set i = 1
set j = 7
for i = 1 to $(j):
TEMP:
load * from [location of sourcetable];
// SO I LOAD THE SOURCE TABLE IN EACH LOOP AS A TEMP TABLE
PRODUCT_$(i):
NoConcatenate
load *
where PRODUCT = $(i)
order by PRICE asc, X desc
RESIDENT TEMP;
// I SHORTEN THE TEMP TABLE AND ORDER IT
drop table TEMP;
// I DROP THE TEMP TABLE
next;
----------------------------------------------------------------------------------------------------------------------------------------------------------------
However from the second iteration i get the error that the TEMP table could not be found and if i place the drop statement outside the loop it starts creating synthetic keys (which is understandable).
Thanks in advance 🙂
