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

[#1]

Hi,

I have excel data sheet in following format...

Country	Product	Sub-Product	Units	Q1'14	Q2'14	Q3'14	Q4'14	FY'2014	So on...
ABC	XYZ	X1Y1Z1	xx

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,

[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

[#3]

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...

Quarter	Year
Q1	2011
Q2	2012
Q3	2013
Q4	2014 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

[#5]

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

[#6]

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 · Accepted solution]

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.

Regards

Anand

[#8]

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

[#9]

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