22 Replies Latest reply: Oct 29, 2010 12:10 PM by Daniel Munoz RSS

    FORMATING DATE - HELP

    ANGUYEN100

      Hi

       

      I'm new to qlikview, having used this over a few days I am getting slightly frustrating with the script language. I am trying to format the month to return a Text rather than a number.

       

      With my script below, it returns a number, not text. Can someone help????

       

       

      SET DateFormat='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
      SET DateFormat='DD/MM/YYYY';
      SQL SELECT `se_AP`,
      `se_DATEIMPORT`,
      `se_FUND`,
      `se_FundNAV`,
      `se_ID`,
      `se_IMPORTID`,
      `se_SwapMTM`,
      `se_SwapMTMFundNAVPer`,
      `se_SwapMTMNotionalPer`,
      `se_SwapNotional`,
      Year(se_DATEIMPORT)as TYEAR,
      Month (se_DATEIMPORT)as TMONTH

      FROM `dbo_SwapExposure`;

        • FORMATING DATE - HELP
          Neil Miller

          Month() returns the numeric month of the date. MonthName() should return the month abbreviation plus the year.

          Left(MonthName(se_DATEIMPORT), 3) as TMONTH should give you just the month abbreviation.

            • FORMATING DATE - HELP
              ANGUYEN100

              Hi

              Thanks for getting back to me

               

              Left(MonthName(se_DATEIMPORT),3) as TMONTH

              I entered this in and I get an error

               

               

                • FORMATING DATE - HELP
                  Neil Miller

                  What is the error? What is the format of se_DATEIMPORT?

                  Try entering this:

                  Left(MonthName(Now()), 3) As TodayMonth


                  I did that in a test and it worked. It could be that it is not recognizing se_DATEIMPORT as a Date. You may have to use Date() or Date#() to convert it first.

                    • FORMATING DATE - HELP
                      ANGUYEN100

                      Hi

                       

                      This is the format of the date field = 22/09/2010

                      Im new to script writing on qlikview

                       

                      How would I edit this query so it returns the abbreivation name of the month rather than number

                       

                      SET DateFormat='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
                      SET DateFormat='DD/MM/YYYY';
                      SQL SELECT `se_AP`,
                      `se_DATEIMPORT`,
                      `se_FUND`,
                      `se_FundNAV`,
                      `se_ID`,
                      `se_IMPORTID`,
                      `se_SwapMTM`,
                      `se_SwapMTMFundNAVPer`,
                      `se_SwapMTMNotionalPer`,
                      `se_SwapNotional`,
                      Year(se_DATEIMPORT)as TYEAR,
                      Left(MonthName(se_DATEIMPORT),3) as TMONTH

                      FROM `dbo_SwapExposure`;

                       

                       

                       

                       

                        • FORMATING DATE - HELP

                          Hi,

                          You can use following script :

                          Left(Monthname(Date#(se_DATEIMPORT,'DD-MMM-YY')),3) as TMONTH

                          If this doesn't work, remove # after Date in above script.

                          Still if it gives error, Plz share sample data if you can.

                          Happy Thoughts

                            • FORMATING DATE - HELP
                              ANGUYEN100

                              Hi

                              Still no luck. I must be doing something wrong. This is what I am trying to run now

                              SET DateFormat='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
                              SET DateFormat='DD/MM/YYYY';
                              SQL SELECT `se_AP`,
                              `se_DATEIMPORT`,
                              `se_FUND`,
                              `se_FundNAV`,
                              `se_ID`,
                              `se_IMPORTID`,
                              `se_SwapMTM`,
                              `se_SwapMTMFundNAVPer`,
                              `se_SwapMTMNotionalPer`,
                              `se_SwapNotional`,
                              Year(se_DATEIMPORT)as TYEAR,
                              Left(Monthname(Date#(se_DATEIMPORT,'DD-MMM-YY')),3) as TMONTH

                              FROM `dbo_SwapExposure`;

                                • FORMATING DATE - HELP
                                  ANGUYEN100

                                  This is my error:

                                  SQL Error:[Microsoft][ODBC Microsoft Access Driver] Syntax error in date in query expression 'Left(Monthname(Date#(se_DATEIMPORT,'DD-MMM-YY')),3)'.
                                  SQL Scriptline:
                                  SQL State:37000
                                  SQL SELECT `se_AP`,
                                  `se_DATEIMPORT`,
                                  `se_FUND`,
                                  `se_FundNAV`,
                                  `se_ID`,
                                  `se_IMPORTID`,
                                  `se_SwapMTM`,
                                  `se_SwapMTMFundNAVPer`,
                                  `se_SwapMTMNotionalPer`,
                                  `se_SwapNotional`,
                                  Year(se_DATEIMPORT)as TYEAR,
                                  Left(Monthname(Date#(se_DATEIMPORT,'DD-MMM-YY')),3) as TMONTH

                                  FROM `dbo_SwapExposure`

                                    • FORMATING DATE - HELP

                                      Hi

                                       

                                      MonthName is a Qlikview function, not SQL

                                       

                                      you have to load first in a table T1 from SQL query and then you can use T2 : LOad *, left(monthname(....) as Tmonth resident T1

                                       

                                      you can make it in one load using

                                       

                                      Load *, left(monthname(....) as Tmonth;

                                      SQL ....FROM dbo_SwapExposure

                                       

                                      regards

                                      christian

                                       

                                       

                                        • FORMATING DATE - HELP
                                          ANGUYEN100

                                          Hi

                                           

                                          This is what I wrote and tried to load butit didn't work

                                           

                                          ODBC CONNECT TO [MS Access Database;DBQ=G:\ETP_TRADING AND OPERATIONS\Daily internal reports\Reporting DB\IM Access Database\ASSENAGON.mdb];
                                          LOAD `se_ID`,
                                          `se_FUND`,
                                          `se_AP`,
                                          `se_SwapNotional`,
                                          `se_FundNAV`,
                                          `se_SwapMTM`,
                                          `se_SwapMTMNotionalPer`,
                                          `se_SwapMTMFundNAVPer`,
                                          Left(Monthname(Date#(se_DATEIMPORT,'DD-MMM-YY')),3) as TMONTH,
                                          `se_IMPORTID`;

                                          SQL SELECT `se_ID`,
                                          `se_FUND`,
                                          `se_AP`,
                                          `se_SwapNotional`,
                                          `se_FundNAV`,
                                          `se_SwapMTM`,
                                          `se_SwapMTMNotionalPer`,
                                          `se_SwapMTMFundNAVPer`,
                                          `se_DATEIMPORT`,
                                          `se_IMPORTID`
                                          Year(TMONTH)as YEAR,
                                          MONTH(TMONTH)as REPORTING_MONTH
                                          FROM `dbo_SwapExposure`;

                                            • FORMATING DATE - HELP
                                              ANGUYEN100

                                              Can you possiblily show me how to set up the script

                                                • FORMATING DATE - HELP

                                                  For me it looks like TMONTH is not a field from Database but a result of a Qlikview function

                                                  so try this

                                                  T1:

                                                  SQL SELECT `se_ID`,
                                                  `se_FUND`,
                                                  `se_AP`,
                                                  `se_SwapNotional`,
                                                  `se_FundNAV`,
                                                  `se_SwapMTM`,
                                                  `se_SwapMTMNotionalPer`,
                                                  `se_SwapMTMFundNAVPer`,
                                                  `se_DATEIMPORT`,
                                                  `se_IMPORTID`
                                                  FROM dbo

                                                   

                                                  T2:

                                                  LOAD

                                                  *,

                                                  Left(Monthname(Date#(se_DATEIMPORT,'DD-MMM-YY')),3) as TMONTH,
                                                  year(se_DATEIMPORT) as Tyear

                                                  resident T1

                                                    • FORMATING DATE - HELP
                                                      ANGUYEN100

                                                      Ok gave this a try

                                                       

                                                      SET DateFormat='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
                                                      SET DateFormat='DD/MM/YYYY';

                                                      Table1:
                                                      SQL SELECT `se_AP`,
                                                      `se_DATEIMPORT`,
                                                      `se_FUND`,
                                                      `se_FundNAV`,
                                                      `se_ID`,
                                                      `se_IMPORTID`,
                                                      `se_SwapMTM`,
                                                      `se_SwapMTMFundNAVPer`,
                                                      `se_SwapMTMNotionalPer`,
                                                      `se_SwapNotional`
                                                      FROM `dbo_SwapExposure`;

                                                      Table2:
                                                      LOAD

                                                      *,
                                                      Left(Monthname(Date#(se_DATEIMPORT,'DD-MMM-YY')),3) as TMONTH,
                                                      year(se_DATEIMPORT) as Tyear

                                                      Resident table1

                                                       

                                                       

                                                      However an error has come up saying

                                                      Table not found
                                                      Table2:
                                                      LOAD

                                                      *,
                                                      Left(Monthname(Date#(se_DATEIMPORT,'DD-MMM-YY')),3) as TMONTH,
                                                      year(se_DATEIMPORT) as Tyear

                                                      Resident table1

                                                        • FORMATING DATE - HELP
                                                          Jonathan Dienst

                                                          The table names in Qlikview aere case sensitive.

                                                          Change the line saying Resident table1 to Resident Table1

                                                          Jonathan

                                                            • FORMATING DATE - HELP
                                                              ANGUYEN100

                                                              Ok... it has successfully loaded however, the TMONTH field is blank now

                                                               

                                                               

                                                                • FORMATING DATE - HELP
                                                                  ANGUYEN100

                                                                  Can someone help? Now I'm left with the new field being left blank.....

                                                                   

                                                                  Not sure what else I can do

                                                                    • FORMATING DATE - HELP
                                                                      Neil Miller

                                                                      Try simplifying the field just to get some data.

                                                                      LOAD
                                                                      *,
                                                                      Left(Monthname(Date#(se_DATEIMPORT,'DD-MMM-YY')),3) as TMONTH,
                                                                      Monthname(Date#(se_DATEIMPORT,'DD-MMM-YY')) as TMONTH1,
                                                                      Date#(se_DATEIMPORT,'DD-MMM-YY') as TMONTH2,
                                                                      se_DATEIMPORT as TMONTH3,
                                                                      year(se_DATEIMPORT) as Tyear

                                                                      Resident Table1


                                                                      Do any of those return data?

                                                                        • FORMATING DATE - HELP
                                                                          ANGUYEN100

                                                                          Thanks ! omg it works now....

                                                                          and now i got another problem....

                                                                          Basically it's a similar problem but I think my SQL is inefficient because it keeps crashing

                                                                           

                                                                          I need to concatenate two tables then change the date format because it's returning numbers. But the problem is..... the query keeps crashing so I think what I have scripted is incorrect...

                                                                          Example:

                                                                          MasterTurnover:
                                                                          SQL SELECT "msamk_id",
                                                                          "msamk_ticker" as Key_Turnoverstatic,
                                                                          "msamk_isin",
                                                                          "msamk_dateimported",
                                                                          "msamk_startdate" as TURNOVER_DATE,
                                                                          "msamk_enddate",
                                                                          "msamk_broker",
                                                                          "msamk_currency",
                                                                          "msamk_srcname",
                                                                          "msamk_markitname",
                                                                          "msamk_turnoverbroker",
                                                                          "msamk_turnovertotal",
                                                                          "msamk_brokerpercent",
                                                                          "msamk_brokerrank",
                                                                          "msamk_1st",
                                                                          "msamk_2nd",
                                                                          "msamk_3rd",
                                                                          "SSMA_TimeStamp"
                                                                          FROM msamarkit.dbo.msamarkit;

                                                                          CONCATENATE ("MasterTurnover")

                                                                          Turnoverbloomberg:
                                                                          SQL SELECT "mastproav_id",
                                                                          BBTicker as Key_Turnoverstatic,
                                                                          "FUND_TOTAL_ASSETS",
                                                                          "FUND_TOTAL_ASSETS_CRNCY",
                                                                          "FUND_TOTAL_ASSETS_EUR",
                                                                          "EQY_TURNOVER",
                                                                          CRNCY,
                                                                          "EQY_TURNOVER_EUR",
                                                                          "PX_LAST",
                                                                          "mastproav_Impid",
                                                                          "mastproav_date" as TURNOVER_DATE,
                                                                          "FUND_TOTAL_ASSETS_DT",
                                                                          "FUND_NET_ASSET_VAL",
                                                                          "NAV_CRNCY",
                                                                          "FUND_NET_ASSET_VAL_EUR",
                                                                          "FUND_NAV_DT",
                                                                          "EQY_SH_OUT"
                                                                          FROM AUM.dbo.masterprodaumvol;

                                                                          Table1:
                                                                          LOAD

                                                                          *,
                                                                          Left(Monthname(Date(TURNOVER_DATE,'DD/MM/YYYY')),3) as TMONTH,
                                                                          year(TURNOVER_DATE) as Tyear

                                                                          Resident MasterTurnover

                                                                            • FORMATING DATE - HELP

                                                                              Concatanate is onyl really for tables which have the same fields (ala "union all"), i have a feeling you actually want to join the tables on the Key_Turnoverstatic and TURNOVER_DATE fields. Try:

                                                                              MasterTurnover:
                                                                              SQL SELECT "msamk_id",
                                                                              "msamk_ticker" as Key_Turnoverstatic,
                                                                              "msamk_isin",
                                                                              "msamk_dateimported",
                                                                              "msamk_startdate" as TURNOVER_DATE,
                                                                              "msamk_enddate",
                                                                              "msamk_broker",
                                                                              "msamk_currency",
                                                                              "msamk_srcname",
                                                                              "msamk_markitname",
                                                                              "msamk_turnoverbroker",
                                                                              "msamk_turnovertotal",
                                                                              "msamk_brokerpercent",
                                                                              "msamk_brokerrank",
                                                                              "msamk_1st",
                                                                              "msamk_2nd",
                                                                              "msamk_3rd",
                                                                              "SSMA_TimeStamp"
                                                                              FROM msamarkit.dbo.msamarkit;

                                                                              left join (MasterTurnover)

                                                                              SQL SELECT "mastproav_id",
                                                                              BBTicker as Key_Turnoverstatic,
                                                                              "FUND_TOTAL_ASSETS",
                                                                              "FUND_TOTAL_ASSETS_CRNCY",
                                                                              "FUND_TOTAL_ASSETS_EUR",
                                                                              "EQY_TURNOVER",
                                                                              CRNCY,
                                                                              "EQY_TURNOVER_EUR",
                                                                              "PX_LAST",
                                                                              "mastproav_Impid",
                                                                              "mastproav_date" as TURNOVER_DATE,
                                                                              "FUND_TOTAL_ASSETS_DT",
                                                                              "FUND_NET_ASSET_VAL",
                                                                              "NAV_CRNCY",
                                                                              "FUND_NET_ASSET_VAL_EUR",
                                                                              "FUND_NAV_DT",
                                                                              "EQY_SH_OUT"
                                                                              FROM AUM.dbo.masterprodaumvol;

                                                                              left join (MasterTurnover)


                                                                              LOAD
                                                                              Left(Monthname(Date(TURNOVER_DATE,'DD/MM/YYYY')),3) as TMONTH,
                                                                              year(TURNOVER_DATE) as Tyear

                                                                              Resident MasterTurnover

                                                                               

                                                                                • FORMATING DATE - HELP
                                                                                  ANGUYEN100

                                                                                  =

                                                                                   

                                                                                  Hi thanks for respondong

                                                                                  What I am trying to do is an append query. MasterTurnover share some fieldsthe same fields

                                                                                  Normal 0 false false false EN-GB X-NONE X-NONE MicrosoftInternetExplorer4 Key_Turnoverstatic,

                                                                                  Normal 0 false false false EN-GB X-NONE X-NONE MicrosoftInternetExplorer4 TURNOVER_DATE

                                                                                  and Normal 0 false false false EN-GB X-NONE X-NONE MicrosoftInternetExplorer4 "msamk_turnoverbroker"= Normal 0 false false false EN-GB X-NONE X-NONE MicrosoftInternetExplorer4 "FUND_NET_ASSET_VAL_EUR",

                                                                            • FORMATING DATE - HELP
                                                                              Daniel Munoz

                                                                              Hi,

                                                                              I think you might want to do a preceding load for each of the SELECTs to modify the date on-the-fly before concatenating both tables.

                                                                               

                                                                              Try this code:

                                                                              MasterTurnover:
                                                                              LOAD
                                                                              *,
                                                                              date(NewDate,'MMM') AS TMONTH,
                                                                              year(NewDate) AS Tyear;
                                                                              LOAD
                                                                              *,
                                                                              num(date#(TURNOVER_DATE,'DD-MM-YYYY')) AS NewDate;
                                                                              SQL SELECT "msamk_id",
                                                                              "msamk_ticker" as Key_Turnoverstatic,
                                                                              "msamk_isin",
                                                                              "msamk_dateimported",
                                                                              "msamk_startdate" as TURNOVER_DATE,
                                                                              "msamk_enddate",
                                                                              "msamk_broker",
                                                                              "msamk_currency",
                                                                              "msamk_srcname",
                                                                              "msamk_markitname",
                                                                              "msamk_turnoverbroker",
                                                                              "msamk_turnovertotal",
                                                                              "msamk_brokerpercent",
                                                                              "msamk_brokerrank",
                                                                              "msamk_1st",
                                                                              "msamk_2nd",
                                                                              "msamk_3rd",
                                                                              "SSMA_TimeStamp"
                                                                              FROM msamarkit.dbo.msamarkit;

                                                                               

                                                                              Turnoverbloomberg:
                                                                              CONCATENATE (MasterTurnover)
                                                                              LOAD
                                                                              *,
                                                                              date(NewDate,'MMM') AS TMONTH,
                                                                              year(NewDate) AS Tyear;
                                                                              LOAD
                                                                              *,
                                                                              num(date#(TURNOVER_DATE,'DD-MM-YYYY')) AS NewDate;
                                                                              SQL SELECT "mastproav_id",
                                                                              BBTicker as Key_Turnoverstatic,
                                                                              "FUND_TOTAL_ASSETS",
                                                                              "FUND_TOTAL_ASSETS_CRNCY",
                                                                              "FUND_TOTAL_ASSETS_EUR",
                                                                              "EQY_TURNOVER",
                                                                              CRNCY,
                                                                              "EQY_TURNOVER_EUR",
                                                                              "PX_LAST",
                                                                              "mastproav_Impid",
                                                                              "mastproav_date" as TURNOVER_DATE,
                                                                              "FUND_TOTAL_ASSETS_DT",
                                                                              "FUND_NET_ASSET_VAL",
                                                                              "NAV_CRNCY",
                                                                              "FUND_NET_ASSET_VAL_EUR",
                                                                              "FUND_NAV_DT",
                                                                              "EQY_SH_OUT"
                                                                              FROM AUM.dbo.masterprodaumvol;

                                                                              DROP FIELD NewDate;

                                                                               

                                                                              Tell me if that works for you, if not, I'll be happy to reply.

                                                                            • FORMATING DATE - HELP

                                                                              Why not use

                                                                              SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec'

                                                                              Then use MonthName()

                                                                               

                                                                          • FORMATING DATE - HELP
                                                                            bryankoch

                                                                            I don't know if this makes a difference, but I know I did exactly what you're trying to do now before:

                                                                            for the headers I have

                                                                            SET DateFormat='M/D/YYYY';
                                                                            SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
                                                                            SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

                                                                             

                                                                            I see you are using

                                                                            SET DateFormat='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
                                                                            SET DateFormat='DD/MM/YYYY';

                                                                             

                                                                            maybe change

                                                                            SET DateFormat='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

                                                                            to

                                                                            SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

                                                                             

                                                                            Then for your table load:

                                                                             

                                                                            Load

                                                                            se_DATEIMPORT, month(se_DATEIMPORT) as MDateImport,

                                                                            Resident