CrossTable -Lable Date

[priya945]

Hi All,

I have data in below cross table format.

Test:
Load * Inline [

Region, 1/1/2019,12/31/2018,11/30/2018
East, 1, 3, 2
West, 0, 1, 4
North, 3, 0, 1
South, 5, 2, 0
];

Could some one help me to get the data shown in below format( changing the date format) 

Region	1-Jan-19	31-Dec-18	30-Nov-18
East	1	3	2
West	0	1	4
North	3	0	1
South	5	2	0

Thanks In advance.

[priya945]

Thanks sunny for coming back.

Suppose if i want to load below data only for last 2 month dates values , how to achieve this in script

Test2:

Load * Inline [

Region, CaseNumber, Date, 1/1/2019, 12/31/2018, 11/30/2018
East, 123, 1/1/2019, 1, 3, 2,
West, 234, 12/31/2018, 0, 1, 4,
North, 345 , 11/30/2018, 3, 0, 1,
South, 456, 10/31/2018, 5, 2, 0,

];

Test3:

CrossTable(RDate,Value,3)
Load * Resident Test2;

[sunny_talwar]

Which two dates are you planning to load this for? Can you share how the data should look after it is loaded based on the sample provided?

[priya945]

Hi Sunny,

I want to load latest 2 dates as below. Could you please guide me to achieve this in the script

Region	North	South	West	East
Date	1-Jan-19	31-Dec-18	1-Jan-19	31-Dec-18	1-Jan-19	31-Dec-18	1-Jan-19	31-Dec-18
count of case Number						1	1
Count of Value

THanks In advance

[sunny_talwar]

This?

Test2:
LOAD * INLINE [
    Region, CaseNumber, Date, 1/1/2019, 12/31/2018, 11/30/2018
    East, 123, 1/1/2019, 1, 3, 2
    West, 234, 12/31/2018, 0, 1, 4
    North, 345, 11/30/2018, 3, 0, 1
    South, 456, 10/31/2018, 5, 2, 0
];

Test3:
CrossTable(RDate, Value, 3)
LOAD *
Resident Test2;

Test4:
NoConcatenate
LOAD Region,
	 CaseNumber,
	 Date,
	 Date#(RDate, 'M/D/YYYY') as RDate,
	 Value
Resident Test3;

DROP Table Test2, Test3;

Temp:
LOAD Region,
	 CaseNumber,
	 Date,
	 Max(RDate) as RDate
Resident Test4
Group By Region, CaseNumber, Date;

Concatenate(Temp)
LOAD Region,
	 CaseNumber,
	 Date,
	 Max(RDate, 2) as RDate
Resident Test4
Group By Region, CaseNumber, Date;

Right Join (Test4)
LOAD *
Resident Temp;

DROP Table Temp;