Create a new table with loops and left joins

[ruben_moya]

Dear Experts

I've got two tables. The first one is a table that I receive showing some information about users:

user	key	cod
1	202	Area
2	202	Area
3	1031	Plaza
4	1029	Plaza
5	1031	Plaza

The second one, a master table:

Del	Area	Plaza
1	202	1031
2	202	1031
3	203	1031
4	202	1031
5	202	1031
6	203	1031
7	205	1029
8	205	1029
9	205	1029
10	205	1029

I need to end up showing for each user a all the Del that they belong to.

user	Del
1	1
1	2
1	4
1	5
2	1
2	2
2	3
2	4
2	5
3	1
3	2
3	3
3	4
3	5
3	6
4	7
4	8
4	9
4	10
5	1
5	2
5	3
5	4
5	5
5	6

I have already solved this problem with left joins and concatenations.

HOWEVER, I've tried to make the script less confusing using a loop:

tableA:
LOAD
user,
key,
cod
FROM excelfile;

FOR EACH a IN FieldValueList('cod')

let vtest=a;

left Join (tableA)
load
$(vtest) as  key,
Del;
SQL SELECT *
FROM DB

NEXT a;

what I have in mind, is for each value of cod (Area or Plaza) store it in a variable and use it to load the name of that dimension in my master table. The problem is that I haven't been able to store the values in the field 'cod' in a variable.

Thanks in advance for your help, i hope i explained myself clearly enough

[kaanerisen · Accepted solution]

Hi Ruben,

Actually you don't need to make left join and loops to get the expected output. Unpivoting the second table by using crosstable function and make an inner join with first table would be enough 🙂

Master:
CrossTable(cod,key)
LOAD
    Del,
    Area,
    Plaza
FROM [lib://web5]
(html, utf8, embedded labels, table is @2);

Inner Join(Master)
LOAD
    user,
    key,
    cod
FROM [lib://web5]
(html, utf8, embedded labels, table is @1);

drop Fields key,cod from Master;

Output :

Hope it helps,

[ruben_moya]

Thanks a lot for your answer. I wasn't familiar with crosstable function. I've been tryng to look for examples but I still dont really understand how did the inner join and crosstable did that result. Could you give me a few concepts so I can understand it? thanks a lot!