Loading multiple sheets into multiple charts

[bobbydave]

Hi All.

I have one excel sheet that loads 6 sheets. Value is text, not numeric.

Load Value, X, Y  From [ ....excel.xlsx   .... table is sheet1]

Load Value, X, Y  From [ ....excel.xlsx   .... table is sheet2]

Load Value, X, Y  From [ ....excel.xlsx   .... table is sheet3]

Load Value, X, Y  From [ ....excel.xlsx   .... table is sheet4]

Load Value, X, Y  From [ ....excel.xlsx   .... table is sheet5]

Load Value, X, Y  From [ ....excel.xlsx   .... table is sheet6]

where the data from Value, X and Y are all different.

I dont want to change the value of 'Value' as I dont want to create synthetic keys.

I have a container that has 6 charts. What I am trying to achieve is have the data from Value from Sheet1 go into Chart1, Value from Sheet2 to go into Chart3 and so on.

However when I attempt this, Value from Sheet 1 to 6 is entered into Chart 1 and the same for the other charts.

Is there a way around this so that only Sheet 1 data goes into Chart 1, Sheet 2 data goes into Chart 2 and so on?

Thanks

[alexandros17 · Accepted solution]

simply add a field to each load ...

Load Value, X, Y, 1 as flag  From [ ....excel.xlsx   .... table is sheet1]

Load Value, X, Y, 2 as flag  From [ ....excel.xlsx   .... table is sheet2]

Load Value, X, Y, 3 as flag  From [ ....excel.xlsx   .... table is sheet3]

Load Value, X, Y, 4 as flag  From [ ....excel.xlsx   .... table is sheet4]

Load Value, X, Y, 5 as flag  From [ ....excel.xlsx   .... table is sheet5]

Load Value, X, Y, 6 as flag  From [ ....excel.xlsx   .... table is sheet6]

Now in each chart use this dimension:

in chart 1:

If(flag=1, flag, null()) and check the "suppress when value is null"

in sheet 2 do with value 2 ...