8 Replies Latest reply: Jan 28, 2015 3:06 AM by Akshay Rai RSS

    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,

        • Re: How to get Year and Quarter in different list box...!!
          Anand Chouhan

          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

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

              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
                • Re: How to get Year and Quarter in different list box...!!
                  Anand Chouhan

                  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

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

                      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

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

                        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

                          • Re: How to get Year and Quarter in different list box...!!
                            Anand Chouhan

                            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

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

                                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

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

                                  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