Merge Three Column Into One Column As "DATE1"

[sunil_sawargave]

Hi Community,

I have two table, here in one table there is one column for "DATE " & in the second Column There are three column like "DAY",  "MONTH", "YEAR ". Here "Day " column of Second Table generated after doing Unpivot the Field.

Here i want to merge Second Table Three  column "Day", "MONTH", "YEAR" into one column As "DATE1" .

Then I want to Mapping the First table "DATE" Column Map with Second Table "DATE 1" Column .

 

Thanks & Regards

Sunil

[rubenmarin]

Hi, after unpivot you can create the date using Makedate() as: Makedate(YEAR,MONTH,DAY) as Date1.

If in table1 you create a composite key with date and shop name, you can create the same composite key in table 2 and join both tables.

Or first do a mapping table using table with the composite key and use Applymap on the first table using the mapping table.

[ajaykakkar93]

hi,

use makedate function 

Sample code:

data1:
//step 1
Mapping load MakeDate(Year,Month,Day) as Date1,
Value;
load * Inline [
Day,Month,Year,Value
02,03,2022,200
22,03,2022,500
14,03,2022,800
21,04,2022,2300
];

data2:
load Date,rand() as rand,
ApplyMap('data1',Date,'Missing') as Value;
load * Inline [
Date,Dim
02/03/2022,dim1
22/03/2022,dim2
14/03/2022,dim3
21/04/2022,dim1
18/04/2022,dim2
29/04/2022,dim3
];

exit Script;

PFA the image attached

[sunil_sawargave]

Hi,

Thank You Rubenmarin,

Where to write this function, in Seperate script or In expression.

Thanks & Regards

Sunil

[rubenmarin]

Hi, all this has to be done in the script.

Regards.

[ajaykakkar93]

u need to write above in data load editor 

[sunil_sawargave]

hi,

Thanks Ajay Kakkar, 

Can i create new separate script & write on that ,is it ok ,

data1:
//step 1
Mapping load MakeDate(Year,Month,Day) as Date1,
Value;
load * Inline [
Day,Month,Year,Value
02,03,2022,200
22,03,2022,500
14,03,2022,800
21,04,2022,2300
];

data2:
load Date,rand() as rand,
ApplyMap('data1',Date,'Missing') as Value;
load * Inline [
Date,Dim
02/03/2022,dim1
22/03/2022,dim2
14/03/2022,dim3
21/04/2022,dim1
18/04/2022,dim2
29/04/2022,dim3
];

exit Script;