Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
priya945
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
sunny_talwar

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
sunny_talwar

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

Anil_Babu_Samineni

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

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)
priya945
Creator
Creator
Author

Hi Sunny,

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

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

. PFA

 

Thanks

 

 
 

 

priya945
Creator
Creator
Author

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 

sunny_talwar

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
Anil_Babu_Samineni

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

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)
priya945
Creator
Creator
Author

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

 

 

priya945
Creator
Creator
Author

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.

sunny_talwar

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