Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

CrossTable -Lable Date

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-1931-Dec-1830-Nov-18
East132
West014
North301
South520

 

Thanks In advance.

Labels (1)
1 Solution

Accepted Solutions
Highlighted

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

View solution in original post

13 Replies
Highlighted

All you want is to format the date differently? Is that the goal here?

Highlighted

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')

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Creator
Creator

Hi Sunny,

Expected o/p is to use pivot table and formatting date as below

1-Jan-1931-Dec-1830-Nov-18

. PFA

 

Thanks

 

 
 

 

Highlighted
Creator
Creator

date.JPG

above is the expected o/p in pivot table with date formatting as below . 

1-Jan-1931-Dec-1830-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 

Highlighted

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

View solution in original post

Highlighted

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
];

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Creator
Creator

Thanks sunny that worked. Some issue with my original data.

 

 

Highlighted
Creator
Creator

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.

Highlighted

Can you elaborate what you mean that there is no connection between Date and RDate? What exactly is your goal?