11 Replies Latest reply: Mar 14, 2017 3:02 AM by Vineeth Pujari RSS

    how to change two different type of date format?

    Agrim Sharma

      Hi All,

       

      i have an date field in excel but it has two type of date formats like follows

      2/27/2017 12:39:36 PM

      03-06-2017  13:16:24

       

      i have created an field as "Today()" for today's date, my objective is to find values which has interval difference more than 2 months between [Last login Time] and Today() so i used 

      (Floor((Today()) - Date([Last login Time], 'MM/DD/YYYY hh:mm:ss'))/30 > 2)

       

      my set analysis in qlik scripts is like this(Main)

      SET TimeFormat='hh:mm:ss ttt';

      SET DateFormat='DD/MM/YYYY';

      SET TimestampFormat='dd-mm-yyyy h:mm:ss';

       

      and my script is like this:

       

      A:

      LOAD

         "Pre-W2K Name",

         if ("Pre-W2K Name"  like 'User*' , 'USER',

         if("Pre-W2K Name" like 'ADM*' , 'ADM',

         if("Pre-W2K Name" like 'BAL*' , 'BAL',

         if("Pre-W2K Name" like 'GEN*' , 'GEN',

         if("Pre-W2K Name" like 'VC*' , 'VC',

         if("Pre-W2K Name" like 'ADP*' , 'ADP',

         if("Pre-W2K Name" like 'ROOM_*' , 'Room',

         if("Pre-W2K Name" like 'FORMATION*' , 'Formation',

         if("Pre-W2K Name" like 'DESK_*' , 'Desk',

         if("Pre-W2K Name" like 'RT_*' , 'Round Table',

         if("Pre-W2K Name" like 'SUPPORT_*' , 'Support',

         if("Pre-W2K Name" like 'DEV_*' , 'Dev', 

         if("Pre-W2K Name" like 'SVC*', 'SVC'))))))))))))) as ACCOUNTGROUP,

        

      IF(IsNull(Role), 'Yes','No') as "Active Account" ,

          "Role"

         "Display Name",

         "Last Logon",

         "Exchange Alias",

         

         Role,

         DN,

         City,

        Today(),

        TextBetween( DN, 'CN=', ',') as [TypeName],

        TextBetween( DN, 'OU=', ',') as [SiteName],

        TextBetween( DN, 'DC=', ',') as DC,

       

          Date ("Last Logon Time Stamp")  as "Last login Time",

         Status,

         "Canonical Name",

         SubField("Canonical Name", '/' , 1) as Roquette,

        SubField("Canonical Name", '/' , 2) as Area,

           SubField("Canonical Name", '/' , 3) as Region,

           SubField("Canonical Name", '/' , 4) as Type,

           SubField("Canonical Name", '/' , 5) as "Name of User"


        FROM [lib://AD Dashboard/AD-alluser with city + Country name Date 07.03.2017.xlsx]

      (ooxml, embedded labels, table is Sheet1);