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: 
Not applicable

How to get Year and Quarter in different list box...!!


Hi,

I have excel data sheet in following format...

CountryProductSub-ProductUnitsQ1'14Q2'14Q3'14Q4'14FY'2014So on...
ABCXYZX1Y1Z1xx

While uploading this file on qlikview the year and quarter value appearing in same listbox. I want to have two list box "Quarter" and 'Year". Please advice the format, in case data sheet need to be transformed or i can get the desire result with the same data sheet.

Regards,

1 Solution

Accepted Solutions
its_anandrjs

Hi,

Yes you can try with cross table i provide you the load script part for the same check the load script. First load cross table and then load the resident table and then extract the Yearfield and Quarted field from the list. And do not load the Year part that is year column 2012, 2013 and 2014 and create SET expression in the front end for this calculation.

T1:
CrossTable(Dates, Data, 4)
LOAD Country,
Product,
Sector,
Units,
[Q1'12],
[Q2'12],
[3Q'12],
[4Q'12],
[2012],
[Q1'13],
[Q2'13],
[3Q'13],
[4Q'13],
[2013],
[Q1'14],
[Q2'14]
FROM
[Dummy File.xlsx]
(
ooxml, embedded labels, table is Sheet2);

NoConcatenate
T2:
LOAD * Resident T1;
DROP Table T1;

Final:
LOAD
if( IsNum(Left(Dates,2)),1,0) as Flag,
Date(Date#(Right(Dates,2),'YY'),'YYYY') as YearField,
Left(Dates,2) as QtrField,
*
Resident T2
Where Not IsNum(Left(Dates,2));
DROP Table T2;

And in load script use Where Not IsNum(Left(Dates,2)); for not load the year columns.

PivotTab.PNG

Regards

Anand

View solution in original post

8 Replies
its_anandrjs

Hi,

While loading this fields put brackets as [ ] in the fields from this only single fields laoded

Ex:-

LOAD Country,

     Product,

     [Sub-Product],

     Units,

     [Q1'14],

     [Q2'14],

     [Q3'14],

     [Q4'14],

     [FY'2014],

     [So on...]

FROM

Source;

Regards

Anand

Not applicable
Author

Hi Anand,

Thanks for ur input. I have uploaded file in same manner and listbox quarter and year appearing individually. Like... Q1,Q2,Q3,Q4 and 2012.

Where i want the list box in this manner in 2 different list box...which is not appearing in this format, if i am uploading as it is...

QuarterYear
Q12011
Q22012
Q32013
Q42014 and so on
its_anandrjs

Hi,

Can you provide some sample file please for the same that will easy to solve the problem with dummy data is ok. And to see how your data is stored in the table. As i understand you want two fields one is for Year and Quarter.

Regards

Anand

Not applicable
Author

Hi

Anand,

The data is in following format. Sorry couldn't provide data point due to compliance of organization. Hope this much will help you to understand the file.

Regards,

Akshay

Not applicable
Author

Hey Anand,

Can i use cross table function to get the desire reuslt. But, i am not sure using crosstable fn to get three matrix i.e. quarter, year and data...??

Regards,

Akshay

its_anandrjs

Hi,

Yes you can try with cross table i provide you the load script part for the same check the load script. First load cross table and then load the resident table and then extract the Yearfield and Quarted field from the list. And do not load the Year part that is year column 2012, 2013 and 2014 and create SET expression in the front end for this calculation.

T1:
CrossTable(Dates, Data, 4)
LOAD Country,
Product,
Sector,
Units,
[Q1'12],
[Q2'12],
[3Q'12],
[4Q'12],
[2012],
[Q1'13],
[Q2'13],
[3Q'13],
[4Q'13],
[2013],
[Q1'14],
[Q2'14]
FROM
[Dummy File.xlsx]
(
ooxml, embedded labels, table is Sheet2);

NoConcatenate
T2:
LOAD * Resident T1;
DROP Table T1;

Final:
LOAD
if( IsNum(Left(Dates,2)),1,0) as Flag,
Date(Date#(Right(Dates,2),'YY'),'YYYY') as YearField,
Left(Dates,2) as QtrField,
*
Resident T2
Where Not IsNum(Left(Dates,2));
DROP Table T2;

And in load script use Where Not IsNum(Left(Dates,2)); for not load the year columns.

PivotTab.PNG

Regards

Anand

Not applicable
Author

Hey Anand,

Thank you so much. It was great help. Got some more issue..will discuss tomrrow. But before that pplease guide me in following concern.

I am completeing new to qlikview application. Had just a day training on desging on Nov and started with project. But my work demands the development and scripting part as well. In current situtation i can't get any training by org. and depending on self learning. So can you help me, is it any other way to learn by using online material or any other refrences. Or else u can able to provide some material on dvelopment and scrpiting.

Regards,

Akshay

Not applicable
Author

Hey Anand,

I followed your given script. Please find the result attached. In result, i am getting 2022 and 2031 in the yearfield but these  are not in the original file. Also, there is one more category which u can see in dummy file "LTM" for which i need different field, so plz guide for the extended script. I have attached the updated dummy file for the refrence.

Thanks and Regards,

Akshay