Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Try this
Test:
CrossTable (Date, Value)
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
];
Table:
NoConcatenate
LOAD Region,
Date(Date#(Date, 'M/D/YYYY'), 'DD-MMM-YY') as Date,
Value
Resident Test;
DROP Table Test
All you want is to format the date differently? Is that the goal here?
I don't see anything cross table needed here? Is that below table in Pivot table? If so, you need cross table. If you already done the cross table to make DateField may be use this in Pivot table
Date(Date#(DateField, 'MM/DD/YYYY'), 'DD-MMM-YYYY')
Hi Sunny,
Expected o/p is to use pivot table and formatting date as below
1-Jan-19 | 31-Dec-18 | 30-Nov-18 |
. PFA
Thanks
above is the expected o/p in pivot table with date formatting as below .
1-Jan-19 | 31-Dec-18 | 30-Nov-18 |
So we have to use the cross table and then format the date, which i am unable to figure it out. Please help me with the script to format the date after using cross table
Try this
Test:
CrossTable (Date, Value)
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
];
Table:
NoConcatenate
LOAD Region,
Date(Date#(Date, 'M/D/YYYY'), 'DD-MMM-YY') as Date,
Value
Resident Test;
DROP Table Test
Perhaps this?
CrossTable(DateField, Sales)
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
];
Thanks sunny that worked. Some issue with my original data.
Hi Sunny,
After loading below data, there is no link between Date & RDate values. Could you please help to establish the connection between two columns.
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;
Thanks In advance.
Can you elaborate what you mean that there is no connection between Date and RDate? What exactly is your goal?