Assign one value

[jumiprado]

   Hey guys!

I have a problem with two tables.

I have one like this

Key_field	ID	Period	Value
X1-Mar 2018	X1	mar 2018	1000
X2-Mar 2018	X2	mar 2018	1500
X3-Mar 2018	X3	mar 2018	2000
X4-Mar 2018	X4	mar 2018	100
X5-Mar 2018	X5	mar 2018	500

And this other table

Key_field	ID	Period	Category
X1-Mar 2018	X1	mar 2018	Categ1
X2-Mar 2018	X2	Mar 2018	Categ1
X2-Mar 2018	X2	Mar 2018	Categ2
X2-Apr 2018	X2	Apr 2018	Categ2
X3-Mar 2018	X3	Mar 2018	Categ5
X4- Mar 2018	X4	Mar 2018	Categ1
X5- Mar 2018	X5	Mar 2018	Categ 2

and i need to join or applymap the Category for the same key_field (ID+Period) but when i have two values for the same Key_field this duplicate the values.

I need the following result table, where the value should be the same for the next period

Key_field	ID	Period	Value	Category
X1-Mar 2018	X1	mar 2018	1000	Categ1
X2-Mar 2018	X2	mar 2018	1500	Categ2
X3-Mar 2018	X3	mar 2018	2000	Categ5
X4-Mar 2018	X4	mar 2018	100	Categ1
X5-Mar 2018	X5	mar 2018	500	Categ2

Thanks

[neelamsaroha157]

Check this.

[sunny_talwar · Accepted solution]

Try this

Table:

LOAD * INLINE [

    Key_field, ID, Period, Value

    X1-Mar 2018, X1, Mar 2018, 1000

    X2-Mar 2018, X2, Mar 2018, 1500

    X3-Mar 2018, X3, Mar 2018, 2000

    X4-Mar 2018, X4, Mar 2018, 100

    X5-Mar 2018, X5, Mar 2018, 500

];

Left Join (Table)

LOAD Key_field,

ID,

Period,

MaxString(Category) as Category

Group By Key_field, ID, Period;

LOAD * INLINE [

    Key_field, ID, Period, Category

    X1-Mar 2018, X1, Mar 2018, Categ1

    X2-Mar 2018, X2, Mar 2018, Categ1

    X2-Mar 2018, X2, Mar 2018, Categ2

    X2-Apr 2018, X2, Apr 2018, Categ2

    X3-Mar 2018, X3, Mar 2018, Categ5

    X4-Mar 2018, X4, Mar 2018, Categ1

    X5-Mar 2018, X5, Mar 2018, Categ2

];

[jumiprado]

Thanks a lot!

[jumiprado]

Thanks a lot!