Showing alternative data

[iglou]

Hi,

I have 2 tables of data with the following fields:

Table 1:

Category

Item

Table 2:

Category

Alternative_item

My goal is to show, in chart view, a table that shows the category and the items. In another table, to show the alternative items. 

Note that it is possible that same items can be multiple category.

eg.

Table1:

LOAD * Inline [

Category, Item

1, A

1, B

1, C

2, A

2, C

2, D];

Table2:

LOAD * Inline [

Category, Alt_Item

1, A

1, B

1, D

1, E

2, B]

The output, in chart view,

Category, Item

1, D

1, E

2, B

Many thanks

[MayilVahanan · Accepted solution]

Hi

Try like below

Table1:

LOAD *,Category&Item as Key Inline [

Category, Item

1, A

1, B

1, C

2, A

2, C

2, D];

Table2:

LOAD *, if(not Exists(Key, Category&Alt_Item), Alt_Item) as FinalItem Inline [

Category, Alt_Item

1, A

1, B

1, D

1, E

2, B];

Drop field Key;

In Chart, use Category and FinalItem

[iglou]

Thanks! This works just fine!