9 Replies Latest reply: Feb 12, 2015 4:05 AM by Akshay Rai RSS

    Data upload on Qlikview


      Hi,

      Please find the attacched sample file of my data. The time frame is in Month, Quarters, H1 and Year in a single row. Which can't be uploaded on qlikview as such. I want list box of Years, Quarter, Half-year and Year seperately on qlikview. Please, provide me with the upload script.

       

      Thanks and regards,

      Akshay

        • Re: Data upload on Qlikview
          Gysbert Wassenaar

          Use the crosstable function:

           

          CrossTable(Period, Value, 7)

          LOAD * FROM [myexcelfile.xlsx]

          (ooxml, embedded labels, table is Sheet1);

          • Re: Data upload on Qlikview

            Hello Gysbert,

             

            I tried but following is the result.

            Sample1_15th_Feb.png
            Where, i want different list box for Each time frame.

            • Re: Data upload on Qlikview
              Nils Morris

              Hi

              Try this

               

              Tmp:
              CrossTable(Period, Data, 7)
              LOAD *
              FROM
              [Sample File 10th_Feb_2015.xlsx]
              (ooxml, embedded labels, table is Sheet1);

              NoConcatenate

              Test:
              LOAD*,
              Year(Date) As Year,
              Month(Date) As Month,
              If(Len('Q'&Ceil(Month(Date)/3))>1,'Q'& Ceil(Month(Date)/3)) As Quarter,
              If(Len('H'&Ceil(Month(Date)/6))>1,'H'& Ceil(Month(Date)/6)) AS HalfYear;
              Load*,
              Date(MakeDate(TmpYear,Month(TmpMonth)),'YYYY-MM') as Date;
              LOAD *,
              20& Right(Period,2) As TmpYear,
              Date#(If(Not Period like 'Q*' And Not Period like 'H*' And Not Period like 'FY*' ,Left(Period,3)),'MMM') As TmpMonth
              Resident Tmp;

              Drop Table Tmp;
              Drop Fields TmpMonth,TmpYear;

              • Re: Data upload on Qlikview
                Nils Morris

                Hi

                 

                When working with dates it is usually best to move these into a calendar table. In your case the script would look something like this. Using autogenerate functions to generate dates even for days without data. In you example you do not have any days so all transactions will be considered to have taken place on the first of every month. The YTD and LYTD is based on "Today" and generate either a 1 or a 0 based on the period so when used in a calculations you can use these flags to calculate current year or last year. Now if you don't have data for this or lasr year then the script will have to be changed a bit. You can also use SET Analysis for YTD and LYTD functions.

                 

                Tmp:
                CrossTable(Period, Data, 7)
                LOAD *

                FROM
                [C:\Users\nmi\Desktop\Sample File 10th_Feb_2015.xlsx]
                (ooxml, embedded labels, table is Sheet1);

                NoConcatenate

                Test:
                Load*,
                Date(MakeDate(TmpYear,Month(TmpMonth)),'YYYY-MM-DD') as Date;
                LOAD *,
                20& Right(Period,2) As TmpYear,
                Date#(If(Not Period like 'Q*' And Not Period like 'H*' And Not Period like 'FY*' ,Left(Period,3)),'MMM') As TmpMonth
                Resident Tmp;

                Drop Table Tmp;
                Drop Fields TmpMonth,TmpYear;

                 

                ///////////// MinMax Table ////////////
                MinMax:
                LOAD
                Min(Date) As MinDate,
                Max(Date) As MaxDate
                Resident Test;

                LET vMinDate = Num(Peek('MinDate',0,'MinMax'));
                LET vMaxDate = Num(Peek('MaxDate',0,'MinMax'));


                ///////////// Temp Calendar ////////////
                TempCal:
                LOAD
                Date($(vMinDate)+ RowNo() -1) As TempDate
                AutoGenerate
                $(vMaxDate)-$(vMinDate) +1;

                DROP Table MinMax;
                //*************** Master Calendar ***************

                MasterCalendar:
                LOAD
                TempDate AS Date,
                Week(TempDate) AS Week,
                Year(TempDate) AS Year,
                Month(TempDate) AS Month,
                Day(TempDate) AS Day,
                Weekday(TempDate) AS WeekDay,
                If(Len('Q'&Ceil(Month(TempDate)/3))>1,'Q'& Ceil(Month(TempDate)/3)) As Quarter,
                If(Len('H'&Ceil(Month(TempDate)/6))>1,'H'& Ceil(Month(TempDate)/6)) AS HalfYear,
                Date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,
                Week(TempDate)&'-'&Year(TempDate) AS WeekYear,
                inyeartodate(TempDate, Today(), 0) * -1 AS CurYTDFlag,
                inyeartodate(TempDate, Today(), -1) * -1 AS LastYTDFlag

                RESIDENT TempCal
                ORDER BY TempDate ASC;

                DROP TABLE TempCal;