Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

?

.

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Re: how to count values whose interval difference is two months?

LOAD

    "Pre-W2K Name", (Account)

     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 'SVC*', 'SVC'))))))))) as ACCOUNTGROUP,

     if("Role" like 'DISABLED' , 'DISABLED',

   if("Role" like 'DISABLED\NO_PWDEXP' , 'Disabled and Expired Password',

    if("Role" like 'DISABLED\NO_PWDEXP\NO_PWDREQD' , 'Disabled, Password Expired and no password required',

     if("Role" like 'NO_PWDEXP' , 'No Password Expired',

      if("Role" like 'NO_PWDEXP\ENCRYPT' , 'Encrypted',

        if("Role" like 'NO_PWDEXP\NO_PWDREQD' , 'No Password',

        if("Role" like 'NO_PWDEXP\SMART' , 'Smart',

   if("Role" like 'NO_PWDEXP\DELEGATE', 'Delegated')))))))) as ACCOUNTSTATUS,

    "Display Name",

    "Last Logon",

    "Exhange Alias",

    "E-Mail",

       "Lync User",

    Role,

    DN,

    City,

    Today(),

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

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

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

 

    "Last login Time",

    Status

 

FROM [lib://AD Dashboard/All-AD-Accounts.Dashboard.xlsx]

(ooxml, embedded labels, table is Sheet1)

WHERE

      Floor(Today() - Timestamp#([Last Logon], 'M/D/YYYY h:mm:ss tt'))/30 > 2

;  


View solution in original post

7 Replies
Highlighted
Luminary
Luminary

Re: how to count values whose interval difference is two months?

Have you tried updating the number format on your field to 'MM hh:mm:ss'

Highlighted
MVP
MVP

Re: how to count values whose interval difference is two months?

Using your 2 rows of test data

1.png

Logon:

load * Inline [

Account ,  Last Logon ,                      Last login Time,              Interval difference

0109222J ,    6/18/2015 12:55:40 PM ,  2/21/2017 6:34:59 PM  ,        614(days) or 20.467 Month

0109227R  ,  2/26/2017 1:00:15 PM    ,  2/27/2017 1:00:15 PM  ,        1(day) or 0.033 Month

];

Final:

load

  *,

  deltamonths > 2 as flag

  ;

load

  Account,

  [Last Logon],                    

  [Last login Time],

  Timestamp#([Last Logon], 'M/D/YYYY h:mm:ss tt') as t1,

  Timestamp#([Last login Time], 'M/D/YYYY h:mm:ss tt') - Timestamp#([Last Logon], 'M/D/YYYY h:mm:ss tt') as delta,

  Floor(Timestamp#([Last login Time], 'M/D/YYYY h:mm:ss tt') - Timestamp#([Last Logon], 'M/D/YYYY h:mm:ss tt')) as deltadays,

  Floor(Timestamp#([Last login Time], 'M/D/YYYY h:mm:ss tt') - Timestamp#([Last Logon], 'M/D/YYYY h:mm:ss tt'))/30 as deltamonths

Resident Logon;

DROP Table Logon;

Highlighted
Creator III
Creator III

?

.

Highlighted
MVP
MVP

Re: how to count values whose interval difference is two months?

try to add a where filter on your data

maybe you have to change the format code, orange

.....

.....

FROM [lib://AD Dashboard/All-AD-Accounts.Dashboard.xlsx]

(ooxml, embedded labels, table is Sheet1)

WHERE

      Floor(Today() - Timestamp#([Last Logon], 'M/D/YYYY h:mm:ss tt'))/30 > 2

;   

Highlighted
Creator III
Creator III

?

.

Highlighted
MVP
MVP

Re: how to count values whose interval difference is two months?

LOAD

    "Pre-W2K Name", (Account)

     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 'SVC*', 'SVC'))))))))) as ACCOUNTGROUP,

     if("Role" like 'DISABLED' , 'DISABLED',

   if("Role" like 'DISABLED\NO_PWDEXP' , 'Disabled and Expired Password',

    if("Role" like 'DISABLED\NO_PWDEXP\NO_PWDREQD' , 'Disabled, Password Expired and no password required',

     if("Role" like 'NO_PWDEXP' , 'No Password Expired',

      if("Role" like 'NO_PWDEXP\ENCRYPT' , 'Encrypted',

        if("Role" like 'NO_PWDEXP\NO_PWDREQD' , 'No Password',

        if("Role" like 'NO_PWDEXP\SMART' , 'Smart',

   if("Role" like 'NO_PWDEXP\DELEGATE', 'Delegated')))))))) as ACCOUNTSTATUS,

    "Display Name",

    "Last Logon",

    "Exhange Alias",

    "E-Mail",

       "Lync User",

    Role,

    DN,

    City,

    Today(),

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

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

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

 

    "Last login Time",

    Status

 

FROM [lib://AD Dashboard/All-AD-Accounts.Dashboard.xlsx]

(ooxml, embedded labels, table is Sheet1)

WHERE

      Floor(Today() - Timestamp#([Last Logon], 'M/D/YYYY h:mm:ss tt'))/30 > 2

;  


View solution in original post

Highlighted
Creator III
Creator III

?

.