Need to compare 2 values from different table with same structure

[noviceneil]

Hello friends,

I have 2 tables as below,

TABLE_PERIOD1

KEY                PERIOD               VALUE

001_AB          310121                10000

001_XY          310121                20000

001_CD         310121                55000

001_PQ          310121                65000

TABLE_PERIOD2

KEY                PERIOD               VALUE

001_AB         280221                12000

001_XY          280221                24000

001_CD          280221                 51000

001_PQ          280221                75000

I have loaded both tables into a single table 

Now based on the key value, I need to show the difference in a separate table: like,

RESULT TABLE

KEY              CURRENT_VALUE          PREVIOUS_VALUE           DIFFERECE in %

001_AB                    12000                          10000                                (12000-1000)/10000

001_XY                     24000                          20000                                (24000=20000)/20000

001_CD                     51000                          55000                                (51000-55000)/55000

001_PQ                     75000                           65000                               (75000-65000)/65000

could you please help me how can I show like the result table?

[duchezbr]

Do the following Set Analyses satisfy your use case:

[noviceneil]

Hello ,

                 Thanks a lot.I will try and let you know....just one additional question,

If I create 2 new master items with the formula(Set analysis) you have suggested and create an additional one to find out the difference for these 2 master items, that's  does not work? often I find the master items can;t be referenced/used inside another aster items..is that expected?

[rwunderlich]

Beginning with the Feb 21 release, you can reference Master Measures within expressions. 

-Rob

[Saravanan_Desingh]

One Solution is,

TABLE_PERIOD2:
LOAD KEY, VALUE As CURRENT_VALUE;
LOAD * INLINE [
KEY, PERIOD, VALUE
001_AB, 280221, 12000
001_XY, 280221, 24000
001_CD, 280221, 51000
001_PQ, 280221, 75000
];

Left Join(TABLE_PERIOD2)

LOAD KEY, VALUE As PREVIOUS_VALUE;
LOAD * INLINE [
KEY, PERIOD, VALUE
001_AB, 310121, 10000
001_XY, 310121, 20000
001_CD, 310121, 55000
001_PQ, 310121, 65000
];

Left Join(TABLE_PERIOD2)
LOAD KEY, (CURRENT_VALUE - PREVIOUS_VALUE)/PREVIOUS_VALUE As [DIFFERECE in %]
Resident TABLE_PERIOD2
;

[Saravanan_Desingh]

Output:

[noviceneil · Accepted solution]

[Data/202101_Data]:

LOAD
LINK_KEY,
DATA_BLOCK_CODE,
DATA_ROW_CODE,
DATA_HEADER_CODE,
DATA_VALUE as PREVIOUS_VALUE
FROM [lib://Data/202012_Data.csv]

(txt, utf8, embedded labels, delimiter is ',', msq);

Left Join([Data/202101_Data])
LOAD
LINK_KEY,
DATA_BLOCK_CODE,
DATA_ROW_CODE,
DATA_HEADER_CODE,
DATA_VALUE as CURRENT_VALUE
FROM [lib://Data/202101_Data.csv]
(txt, utf8, embedded labels, delimiter is ',', msq);

[noviceneil]

Hello  saran,

                          Thank you...I like your solution but stuck while tried to implement. I tried with the below code but when it gets loaded, I find the CURRENT_VALUE field are all BLANK.

Could you please help if there is any basic mistake in my code?

[Saravanan_Desingh]

Hello,

If the CURRENT_VALUE is Blank, then it looks like there is no match of Keys.

Please check the Four Fields in the First Table has match in the Second Table.

[noviceneil]

Thanks ..it's working