Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
qlikapple
Creator
Creator
‎2020-01-31 06:14 AM

Pivot Table

RegionCountryYearMonth
Asia PacificPhilippines2019Aug
Asia PacificPhilippines2019Dec
Asia PacificPhilippines2019Jul
Asia PacificPhilippines2019Oct
Asia PacificPhilippines2019Sep
Asia PacificPhilippines2020Jan
EuropeUK HSBC2019Apr
EuropeUK HSBC2019Aug
EuropeUK HSBC2019Dec
EuropeUK HSBC2019Jul
EuropeUK HSBC2019June
EuropeUK HSBC2019May
EuropeUK HSBC2019Nov
EuropeUK HSBC2019Oct
EuropeUK HSBC2019Sep
EuropeUK HSBC2020Jan
EuropeUK FD2019Aug
EuropeUK FD2019Dec
EuropeUK FD2019Jul
EuropeUK FD2019June
EuropeUK FD2019May
EuropeUK FD2019Nov
EuropeUK FD2019Oct
EuropeUK FD2019Sep
EuropeUK FD2020Jan
EuropeUK MSB2019Aug
EuropeUK MSB2019Jul
EuropeUK MSB2019June
EuropeUK MSB2019Nov
EuropeUK MSB2019Oct
EuropeUK MSB2019Sep
EuropeUK MSB2020Jan
EuropeUK JLFS2019Aug
EuropeUK JLFS2019Jul
EuropeUK JLFS2019Nov
EuropeUK JLFS2019Oct

 

I need the  table as 

RegionCountryMin to Max
Asia PacificPhilippines2019 Jul-2020 Jan
  EuropeUK HSBC2019 Apr-2020 Jan

 

 

can anyone please help me on this

Labels (1)
Labels
471 Views
0 Likes
3 Replies
qlikapple
Creator
Creator
‎2020-01-31 06:48 AM
Author

anyone please look into this

445 Views
0 Likes
sunny_talwar
MVP
MVP
‎2020-01-31 08:22 AM

In response to qlikapple

Try this

Table:
LOAD Region,
	 Country,
	 Date(Min(YearMonth), 'YYYY MMM') & ' - ' & Date(Max(YearMonth), 'YYYY MMM') as [Min to Max]
Group By Region, Country;
LOAD Region,
	 Country,
	 Year,
	 Month(Date#(Month, 'MMM')) as Month,
	 Date(MakeDate(Year, Month(Date#(Month, 'MMM'))), 'YYYY MMM') as YearMonth;
LOAD * INLINE [
    Region, Country, Year, Month
    Asia Pacific, Philippines, 2019, Aug
    Asia Pacific, Philippines, 2019, Dec
    Asia Pacific, Philippines, 2019, Jul
    Asia Pacific, Philippines, 2019, Oct
    Asia Pacific, Philippines, 2019, Sep
    Asia Pacific, Philippines, 2020, Jan
    Europe, UK HSBC, 2019, Apr
    Europe, UK HSBC, 2019, Aug
    Europe, UK HSBC, 2019, Dec
    Europe, UK HSBC, 2019, Jul
    Europe, UK HSBC, 2019, June
    Europe, UK HSBC, 2019, May
    Europe, UK HSBC, 2019, Nov
    Europe, UK HSBC, 2019, Oct
    Europe, UK HSBC, 2019, Sep
    Europe, UK HSBC, 2020, Jan
    Europe, UK FD, 2019, Aug
    Europe, UK FD, 2019, Dec
    Europe, UK FD, 2019, Jul
    Europe, UK FD, 2019, June
    Europe, UK FD, 2019, May
    Europe, UK FD, 2019, Nov
    Europe, UK FD, 2019, Oct
    Europe, UK FD, 2019, Sep
    Europe, UK FD, 2020, Jan
    Europe, UK MSB, 2019, Aug
    Europe, UK MSB, 2019, Jul
    Europe, UK MSB, 2019, June
    Europe, UK MSB, 2019, Nov
    Europe, UK MSB, 2019, Oct
    Europe, UK MSB, 2019, Sep
    Europe, UK MSB, 2020, Jan
    Europe, UK JLFS, 2019, Aug
    Europe, UK JLFS, 2019, Jul
    Europe, UK JLFS, 2019, Nov
    Europe, UK JLFS, 2019, Oct
];
421 Views
Brett_Bleess
Former Employee
Former Employee
‎2020-02-24 04:48 PM

Did Sunny's post help you get what you needed for your use case?  If so, do not forget to return to your thread and on his post, use the Accept as Solution button to mark it giving him credit for the help and letting other Members know that worked.  If you did something else, please consider posting that and then mark it to close out the thread, and if you are still working on things, please leave an update with what you still need.

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
342 Views
0 Likes