Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

agrimroquette
Not applicable

how to count values whose interval difference is two months?

Hi, All,

i have an data in which, there are some user Accounts and its Last login(MM/DD/YYYY hh:mm:ss) Time & Last Logon(MM/DD/YYYY hh:mm:ss) dates

i want to count such accounts that has interval difference of more than 2 months between Last login & Last Logon

eg. 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

so i want "0109222J" as my desired output as it has interval difference more than 2 months

my script is like this:-

LOAD

    "Account",    

    "Last Logon",

      Role,

    "Last login Time",

    Status

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

(ooxml, embedded labels, table is Sheet1);

Thanks,

Agrim

1 Solution

Accepted Solutions
maxgro
Not applicable

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

;  


10 Replies
jheasley
Not applicable

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'

agrimroquette
Not applicable

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

no, i want to show only those accounts that has time difference of more than 1.99 months

maxgro
Not applicable

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;

agrimroquette
Not applicable

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

Hi Massimo,

thanks for your kind help,

can you tell me where should i place your provided script?

my Script:-

LOAD

    "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 i have inserted Today(),


what if want to get the output from

Today() - "Last Logon" and if output comes more than 2 months

then it will show only those accounts thats interval difference is more than 2 months between Today() and Last Logon

maxgro
Not applicable

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

;   

agrimroquette
Not applicable

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

ok thanks a lot for the help but can you please tell me where should i place your given script in my made script? my script is as follows:-

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);

maxgro
Not applicable

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

;  


agrimroquette
Not applicable

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

i do not have to include this? in my script?


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;

agrimroquette
Not applicable

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

HI,

i used this formula as a measure in a Pie Chart and it gives me output as (-1) and (0)

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

is there any way to replace (-1) as 'Delay'

and (0) as 'On Time'