topic Re: Need to compare 2 values from different table with same structure in App Development

Need to compare 2 values from different table with same structure

noviceneil — Tue, 09 Mar 2021 14:36:20 GMT

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?

Re: Need to compare 2 values from different table with same structure

duchezbr — Tue, 09 Mar 2021 14:56:33 GMT

Do the following Set Analyses satisfy your use case:

Re: Need to compare 2 values from different table with same structure

noviceneil — Tue, 09 Mar 2021 15:17:25 GMT

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?

Re: Need to compare 2 values from different table with same structure

rwunderlich — Tue, 09 Mar 2021 15:48:00 GMT

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

-Rob

Re: Need to compare 2 values from different table with same structure

Saravanan_Desingh — Wed, 10 Mar 2021 03:45:27 GMT

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 ;

Re: Need to compare 2 values from different table with same structure

Saravanan_Desingh — Wed, 10 Mar 2021 03:45:52 GMT

Output:

Re: Need to compare 2 values from different table with same structure

noviceneil — Sun, 14 Mar 2021 19:32:49 GMT

[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);

Re: Need to compare 2 values from different table with same structure

noviceneil — Sun, 14 Mar 2021 20:39:08 GMT

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?

Re: Need to compare 2 values from different table with same structure

Saravanan_Desingh — Mon, 15 Mar 2021 01:42:59 GMT

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.

Re: Need to compare 2 values from different table with same structure

noviceneil — Mon, 15 Mar 2021 10:05:33 GMT

Thanks ..it's working