Transform table in script

[#1]

Hi everyone!

I have a table with the following structure:

table1

Number	Name	Text
1	A-name	A1
1	B-name	B1
1	C-name	C1
2	A-name	A2
2	B-name	B2
2	C-name	C2
3	A-name	A3
3	B-name	B3
3	C-name	C3

Based on table1 I want to get the following table:

Number	A-name	B-name	C-name
1	A1	B1	C1
2	A2	B2	C2
3	A3	B3	C3

My script:

table2:

Load Number,

if(Name = 'A-name', Text) as A-name,

if(Name = 'B-name', Text) as B-name,

if(Name = 'C-name', Text) as C-name

resident table1;

But unfortunately my result is following table:

table2

Number	A-name	B-name	C-name
1	A1	-	-
1	-	B1	-
1	-	-	C1
2	A2	-	-
2	-	B2	-
2	-	-	C2
3	A3	-	-
3	-	B3	-
3	-	-	C3

Can anyone help me?

[Miguel_Angel_Baeyens]

Hi,

If your model is not very big, you can try a generic load in the script, that will leave the data as you expect

Table:
GENERIC LOAD Number, 
     Name, 
     Text
FROM
C:\qcomGenericLoad.xlsx
(ooxml, embedded labels, table is Sheet1);

You can get that in the chart, creating a new pivot table, using Number and Name as dimensions and Text or Only(Text) as expression.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#3]

Hi,

thanks for your answer.

I tried the generic load and it simplifies my script, but the result is still the same (like table2 in my example).

[#4 · Accepted solution]

Not Sure, How you Followed the Miguel`s suggestion. I followed his suggetsion, i could get the proper result.

Check out the attached application.

-Sridhar

[#5]

Sleepa, oops..!! My thread may be helpful Answer and Miguel Angel Baeyens post should be correct one.

Please dont mind to change the correct and helpful answer status

- Sridhar