6 Replies Latest reply: Jul 11, 2011 6:50 AM by Miguel Angel Baeyens de Arce RSS

    Create a Date field

      Hi guys!!!

      I need some help...

       

      I have three Qvds.By concatenating them I am geting a  single table.Month and Year fields are present...but there is no date field (Day field).Data is geting uploaded on regular basis.I want to add a date field so that the user can see the difference between toady's data nd yesterday's data.

        • Create a Date field
          Kaushik Solanki

          Hi,

           

             Can you please share the script file.

           

          Regards,

          Kaushik Solanki

            • Re: Create a Date field

              Here is the script.

                • Create a Date field
                  Kaushik Solanki

                  Hi,

                   

                     You have used MakeDate function to create a date, but you have used only 2 parameter try with 3 Parameter.

                   

                     The one which you used is.

                   

                      MakeDate(NewYear,Daily.FinMonth) as DailyDashboardDate

                   

                      Make it as

                   

                      MakeDate(NewYear,Daily.FinMonth,1) as DailyDashboardDate;

                   

                      So here the first Parameter is Year, Second is Month and Third is Day.

                   

                  Regards,

                  Kaushik Solanki

                    • Re: Create a Date field
                      Miguel Angel Baeyens de Arce

                      Hi,

                       

                      For what it's worth here's no need for that, since MakeDate() will work fine with even one parameter (year) and will return the first possible value for that year.

                       

                      MakeDate(2011, 7)
                      

                       

                      will return the first day for year 2011 month 7. If you do have a field where you store the complete timestamp of the date, then yes, you could use that as the third parameter. All three parameters must be numeric.

                       

                      But in any case, I'd create a unique date field in each table (using MakeDate will do) and link that to a master calendar table, where you have all the date dimensions (year, month, quarter, week, day, whatever else).

                       

                      Here is what I'd do.

                       

                      Dump0:
                      LOAD 
                           Date(MakeDate(Year, Month)) AS Date,
                      ...
                      FROM [E:\Qlikview\Data\RawQVD\GENVCROSS.qvd] (qvd);
                      
                      CONCATENATE LOAD 
                           Date(MakeDate(Year, Month)) AS Date,
                      ...
                      FROM [E:\Qlikview\Data\AppQVD\GTRNCUM.qvd] (qvd);
                      
                      CONCATENATE LOAD
                           Date(MakeDate(Year, Month)) AS Date,
                      ...
                      FROM [E:\Qlikview\Data\AppQVD\GTRNCUMExpenses.qvd] (qvd)
                      WHERE Match(Class, 900, 700) > 0 AND Match(Code, 2000, 3800, 3810, 3900, 2100, 220, 2400, 2320, 2310, 2500, 2700, 2800, 2900, 3451, 3400, 3530, 3300, 2330) > 0;
                      // Using match() instead of the conditional, for clarity and perhaps performance
                      
                      CalendarMinMax:
                      LOAD Max(Date) AS MaxDate,
                           Min(Date) AS MinDate
                      RESIDENT Dump0:
                      
                      // Getting highets and lowest possible values for actual dates
                      LET vMaxDate = FieldValue('MaxDate', 0);
                      LET vMinDate = FieldValue('MinDate', 0);
                      
                      DROP TABLE CalendarMinMax;
                      
                      Calendar:
                      LOAD Date($(vMinDate) + RowNo() -1) AS Date,
                           Year($(vMinDate) + RowNo() -1) AS Year,
                           Month($(vMinDate) + RowNo() -1) AS Month,
                           Week($(vMinDate) + RowNo() -1) AS Week,
                           ... // all the rest of required date dimensions
                      AUTOGENERATE $(vMaxDate) - $(vMinDate) +1;
                      

                       

                      I haven't tested the code and I may have made some syntax mispellings. Anyway, the idea is to have one table you can easily modify to add or remove date fields and a separte fact table.

                       

                      Hope that makes sense and helps.

                       

                      Miguel Angel Baeyens

                      BI Consultant

                      Comex Grupo Ibérica

                        • Re: Create a Date field

                          I am not sure if u hv understood my problm completely..

                          Suppose i have uploaded data today...

                          then month nd Year are being fetched frm data itself....but I want to add day also.

                          since I have uploaded data today then the data should be stored at today's date (Month nd year fetched frm above nd Day will be relaod date.).Now again when i am loading data tomorrow then data should be saved at tomorrow's date.

                           

                          I need this because i want to see increment from Yesterday's data.

                           

                           

                          what i am planning is to do:

                           

                          MonthName(MakeDate(NewYear,Daily.FinMonth,day(reloadtime())),3) as DailyDashboardMonthYear,

                          MakeDate(NewYear,Daily.FinMonth,day(reloadtime())) as DailyDashboardDate;

                           

                          but this will nt wrk as previous 'Day' will get replaced by today's 'Day'

                          and not concatenated.

                           

                          M confused how sholud i do linking and all...

                            • Re: Create a Date field
                              Miguel Angel Baeyens de Arce

                              Hi Erika,

                               

                              I see two different things here: in one hand the "incremental" load based on some timestamp that is not in your database and depends on when the files are loaded and on the other hand the date dimensions used later in your charts and expressions.

                               

                              In regards to the code

                               

                              MonthName(MakeDate(NewYear, Daily.FinMonth, day(reloadtime())), 3) AS DailyDashboardMonthYear
                              

                               

                              That will create a field with the values in NewYear, Daily.FinMonth and today's day number. If all that is meant to be "today", then why not using instead a cleaner and simpler

                               

                              Date(Today()) AS Date
                              

                               

                              You can link that to the Calendar table where you have the month, monthname, and any other date related dimensions.

                               

                              In regards to the load, do you have in your data source some way to identify the complete date of the records so you can use that field in a WHERE clause?

                               

                              You can always do a two step load and store based on the code above, so you always load the QVD file, plus the records where date doesn't exist (something like this), or use a key field to load all records from your source except for those which key has been already loaded... Hence you always have a valid and complete date field you can use in your expressions.

                               

                              Hope that helps.

                               

                              Miguel Angel Baeyens

                              BI Consultant

                              Comex Grupo Ibérica