Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
NicolasRivas
Partner - Contributor II
Partner - Contributor II
2 weeks ago

Decrypt Date

Hello Community,

Recently I discovered that I can make que QlikViewServer Service make a CalData.pgo.xml, so i wanted to do an app to see the document cals assigned and last used, but the Last used date comes encrypte like this:

Community.png

 

I want to know if is there any way to decrypt those dates so I can do some kpis with them.

Thank you.

Labels (3)
Labels
103 Views
1 Reply
rubenmarin
MVP
MVP
a week ago

Hi, you can try with this function:

SET DecryptHex= "If (Len (KeepChar ($1, '0123456789AaBbCcDdEeFf')) <> 16
  , Null ()
  , If (Num (Num# (Mid (Right (Repeat ('0', 32) & Num (Num# (Left ($1, 8), '(HEX)'), '(BIN)'), 32) & Right (Repeat ('0', 32) & Num (Num# (Right ($1, 8), '(HEX)'), '(BIN)'), 32), 2, 11), '(BIN)'), '(DEC)') >= 1
        and Num (Num# (Mid (Right (Repeat ('0', 32) & Num (Num# (Left ($1, 8), '(HEX)'), '(BIN)'), 32) & Right (Repeat ('0', 32) & Num (Num# (Right ($1, 8), '(HEX)'), '(BIN)'), 32), 2, 11), '(BIN)'), '(DEC)') <= 2046
      , (1 +
         Num (Mid (Right (Right (Repeat ('0', 32) & Num (Num# (Left ($1, 8), '(HEX)'), '(BIN)'), 32) & Right (Repeat ('0', 32) & Num (Num# (Right ($1, 8), '(HEX)'), '(BIN)'), 32), 52),  1, 1)) / pow (2,  1) +
         Num (Mid (Right (Right (Repeat ('0', 32) & Num (Num# (Left ($1, 8), '(HEX)'), '(BIN)'), 32) & Right (Repeat ('0', 32) & Num (Num# (Right ($1, 8), '(HEX)'), '(BIN)'), 32), 52),  2, 1)) / pow (2,  2) +
         Num (Mid (Right (Right (Repeat ('0', 32) & Num (Num# (Left ($1, 8), '(HEX)'), '(BIN)'), 32) & Right (Repeat ('0', 32) & Num (Num# (Right ($1, 8), '(HEX)'), '(BIN)'), 32), 52),  3, 1)) / pow (2,  3) +
         Num (Mid (Right (Right (Repeat ('0', 32) & Num (Num# (Left ($1, 8), '(HEX)'), '(BIN)'), 32) & Right (Repeat ('0', 32) & Num (Num# (Right ($1, 8), '(HEX)'), '(BIN)'), 32), 52),  4, 1)) / pow (2,  4) +
         Num (Mid (Right (Right (Repeat ('0', 32) & Num (Num# (Left ($1, 8), '(HEX)'), '(BIN)'), 32) & Right (Repeat ('0', 32) & Num (Num# (Right ($1, 8), '(HEX)'), '(BIN)'), 32), 52),  5, 1)) / pow (2,  5) +
         Num (Mid (Right (Right (Repeat ('0', 32) & Num (Num# (Left ($1, 8), '(HEX)'), '(BIN)'), 32) & Right (Repeat ('0', 32) & Num (Num# (Right ($1, 8), '(HEX)'), '(BIN)'), 32), 52),  6, 1)) / pow (2,  6) +
         Num (Mid (Right (Right (Repeat ('0', 32) & Num (Num# (Left ($1, 8), '(HEX)'), '(BIN)'), 32) & Right (Repeat ('0', 32) & Num (Num# (Right ($1, 8), '(HEX)'), '(BIN)'), 32), 52),  7, 1)) / pow (2,  7) +
         Num (Mid (Right (Right (Repeat ('0', 32) & Num (Num# (Left ($1, 8), '(HEX)'), '(BIN)'), 32) & Right (Repeat ('0', 32) & Num (Num# (Right ($1, 8), '(HEX)'), '(BIN)'), 32), 52),  8, 1)) / pow (2,  😎 +
         Num (Mid (Right (Right (Repeat ('0', 32) & Num (Num# (Left ($1, 8), '(HEX)'), '(BIN)'), 32) & Right (Repeat ('0', 32) & Num (Num# (Right ($1, 8), '(HEX)'), '(BIN)'), 32), 52),  9, 1)) / pow (2,  9) +
         Num (Mid (Right (Right (Repeat ('0', 32) & Num (Num# (Left ($1, 8), '(HEX)'), '(BIN)'), 32) & Right (Repeat ('0', 32) & Num (Num# (Right ($1, 8), '(HEX)'), '(BIN)'), 32), 52), 10, 1)) / pow (2, 10) +
         Num (Mid (Right (Right (Repeat ('0', 32) & Num (Num# (Left ($1, 8), '(HEX)'), '(BIN)'), 32) & Right (Repeat ('0', 32) & Num (Num# (Right ($1, 8), '(HEX)'), '(BIN)'), 32), 52), 11, 1)) / pow (2, 11) +
         Num (Mid (Right (Right (Repeat ('0', 32) & Num (Num# (Left ($1, 8), '(HEX)'), '(BIN)'), 32) & Right (Repeat ('0', 32) & Num (Num# (Right ($1, 8), '(HEX)'), '(BIN)'), 32), 52), 12, 1)) / pow (2, 12) +
         Num (Mid (Right (Right (Repeat ('0', 32) & Num (Num# (Left ($1, 8), '(HEX)'), '(BIN)'), 32) & Right (Repeat ('0', 32) & Num (Num# (Right ($1, 8), '(HEX)'), '(BIN)'), 32), 52), 13, 1)) / pow (2, 13) +
         Num (Mid (Right (Right (Repeat ('0', 32) & Num (Num# (Left ($1, 8), '(HEX)'), '(BIN)'), 32) & Right (Repeat ('0', 32) & Num (Num# (Right ($1, 8), '(HEX)'), '(BIN)'), 32), 52), 14, 1)) / pow (2, 14) +
         Num (Mid (Right (Right (Repeat ('0', 32) & Num (Num# (Left ($1, 8), '(HEX)'), '(BIN)'), 32) & Right (Repeat ('0', 32) & Num (Num# (Right ($1, 8), '(HEX)'), '(BIN)'), 32), 52), 15, 1)) / pow (2, 15) +
         Num (Mid (Right (Right (Repeat ('0', 32) & Num (Num# (Left ($1, 8), '(HEX)'), '(BIN)'), 32) & Right (Repeat ('0', 32) & Num (Num# (Right ($1, 8), '(HEX)'), '(BIN)'), 32), 52), 16, 1)) / pow (2, 16) +
         Num (Mid (Right (Right (Repeat ('0', 32) & Num (Num# (Left ($1, 8), '(HEX)'), '(BIN)'), 32) & Right (Repeat ('0', 32) & Num (Num# (Right ($1, 8), '(HEX)'), '(BIN)'), 32), 52), 17, 1)) / pow (2, 17) +
         Num (Mid (Right (Right (Repeat ('0', 32) & Num (Num# (Left ($1, 8), '(HEX)'), '(BIN)'), 32) & Right (Repeat ('0', 32) & Num (Num# (Right ($1, 8), '(HEX)'), '(BIN)'), 32), 52), 18, 1)) / pow (2, 18) +
         Num (Mid (Right (Right (Repeat ('0', 32) & Num (Num# (Left ($1, 8), '(HEX)'), '(BIN)'), 32) & Right (Repeat ('0', 32) & Num (Num# (Right ($1, 8), '(HEX)'), '(BIN)'), 32), 52), 19, 1)) / pow (2, 19) +
         Num (Mid (Right (Right (Repeat ('0', 32) & Num (Num# (Left ($1, 8), '(HEX)'), '(BIN)'), 32) & Right (Repeat ('0', 32) & Num (Num# (Right ($1, 8), '(HEX)'), '(BIN)'), 32), 52), 20, 1)) / pow (2, 20) +
         Num (Mid (Right (Right (Repeat ('0', 32) & Num (Num# (Left ($1, 8), '(HEX)'), '(BIN)'), 32) & Right (Repeat ('0', 32) & Num (Num# (Right ($1, 8), '(HEX)'), '(BIN)'), 32), 52), 21, 1)) / pow (2, 21) +
         Num (Mid (Right (Right (Repeat ('0', 32) & Num (Num# (Left ($1, 8), '(HEX)'), '(BIN)'), 32) & Right (Repeat ('0', 32) & Num (Num# (Right ($1, 8), '(HEX)'), '(BIN)'), 32), 52), 22, 1)) / pow (2, 22) +
         Num (Mid (Right (Right (Repeat ('0', 32) & Num (Num# (Left ($1, 8), '(HEX)'), '(BIN)'), 32) & Right (Repeat ('0', 32) & Num (Num# (Right ($1, 8), '(HEX)'), '(BIN)'), 32), 52), 23, 1)) / pow (2, 23) +
         Num (Mid (Right (Right (Repeat ('0', 32) & Num (Num# (Left ($1, 8), '(HEX)'), '(BIN)'), 32) & Right (Repeat ('0', 32) & Num (Num# (Right ($1, 8), '(HEX)'), '(BIN)'), 32), 52), 24, 1)) / pow (2, 24) +
         Num (Mid (Right (Right (Repeat ('0', 32) & Num (Num# (Left ($1, 8), '(HEX)'), '(BIN)'), 32) & Right (Repeat ('0', 32) & Num (Num# (Right ($1, 8), '(HEX)'), '(BIN)'), 32), 52), 25, 1)) / pow (2, 25) +
         Num (Mid (Right (Right (Repeat ('0', 32) & Num (Num# (Left ($1, 8), '(HEX)'), '(BIN)'), 32) & Right (Repeat ('0', 32) & Num (Num# (Right ($1, 8), '(HEX)'), '(BIN)'), 32), 52), 26, 1)) / pow (2, 26) +
         Num (Mid (Right (Right (Repeat ('0', 32) & Num (Num# (Left ($1, 8), '(HEX)'), '(BIN)'), 32) & Right (Repeat ('0', 32) & Num (Num# (Right ($1, 8), '(HEX)'), '(BIN)'), 32), 52), 27, 1)) / pow (2, 27) +
         Num (Mid (Right (Right (Repeat ('0', 32) & Num (Num# (Left ($1, 8), '(HEX)'), '(BIN)'), 32) & Right (Repeat ('0', 32) & Num (Num# (Right ($1, 8), '(HEX)'), '(BIN)'), 32), 52), 28, 1)) / pow (2, 28) +
         Num (Mid (Right (Right (Repeat ('0', 32) & Num (Num# (Left ($1, 8), '(HEX)'), '(BIN)'), 32) & Right (Repeat ('0', 32) & Num (Num# (Right ($1, 8), '(HEX)'), '(BIN)'), 32), 52), 29, 1)) / pow (2, 29) +
         Num (Mid (Right (Right (Repeat ('0', 32) & Num (Num# (Left ($1, 8), '(HEX)'), '(BIN)'), 32) & Right (Repeat ('0', 32) & Num (Num# (Right ($1, 8), '(HEX)'), '(BIN)'), 32), 52), 30, 1)) / pow (2, 30) +
         Num (Mid (Right (Right (Repeat ('0', 32) & Num (Num# (Left ($1, 8), '(HEX)'), '(BIN)'), 32) & Right (Repeat ('0', 32) & Num (Num# (Right ($1, 8), '(HEX)'), '(BIN)'), 32), 52), 31, 1)) / pow (2, 31) +
         Num (Mid (Right (Right (Repeat ('0', 32) & Num (Num# (Left ($1, 8), '(HEX)'), '(BIN)'), 32) & Right (Repeat ('0', 32) & Num (Num# (Right ($1, 8), '(HEX)'), '(BIN)'), 32), 52), 32, 1)) / pow (2, 32) +
         Num (Mid (Right (Right (Repeat ('0', 32) & Num (Num# (Left ($1, 8), '(HEX)'), '(BIN)'), 32) & Right (Repeat ('0', 32) & Num (Num# (Right ($1, 8), '(HEX)'), '(BIN)'), 32), 52), 33, 1)) / pow (2, 33) +
         Num (Mid (Right (Right (Repeat ('0', 32) & Num (Num# (Left ($1, 8), '(HEX)'), '(BIN)'), 32) & Right (Repeat ('0', 32) & Num (Num# (Right ($1, 8), '(HEX)'), '(BIN)'), 32), 52), 34, 1)) / pow (2, 34) +
         Num (Mid (Right (Right (Repeat ('0', 32) & Num (Num# (Left ($1, 8), '(HEX)'), '(BIN)'), 32) & Right (Repeat ('0', 32) & Num (Num# (Right ($1, 8), '(HEX)'), '(BIN)'), 32), 52), 35, 1)) / pow (2, 35) +
         Num (Mid (Right (Right (Repeat ('0', 32) & Num (Num# (Left ($1, 8), '(HEX)'), '(BIN)'), 32) & Right (Repeat ('0', 32) & Num (Num# (Right ($1, 8), '(HEX)'), '(BIN)'), 32), 52), 36, 1)) / pow (2, 36) +
         Num (Mid (Right (Right (Repeat ('0', 32) & Num (Num# (Left ($1, 8), '(HEX)'), '(BIN)'), 32) & Right (Repeat ('0', 32) & Num (Num# (Right ($1, 8), '(HEX)'), '(BIN)'), 32), 52), 37, 1)) / pow (2, 37) +
         Num (Mid (Right (Right (Repeat ('0', 32) & Num (Num# (Left ($1, 8), '(HEX)'), '(BIN)'), 32) & Right (Repeat ('0', 32) & Num (Num# (Right ($1, 8), '(HEX)'), '(BIN)'), 32), 52), 38, 1)) / pow (2, 38) +
         Num (Mid (Right (Right (Repeat ('0', 32) & Num (Num# (Left ($1, 8), '(HEX)'), '(BIN)'), 32) & Right (Repeat ('0', 32) & Num (Num# (Right ($1, 8), '(HEX)'), '(BIN)'), 32), 52), 39, 1)) / pow (2, 39) +
         Num (Mid (Right (Right (Repeat ('0', 32) & Num (Num# (Left ($1, 8), '(HEX)'), '(BIN)'), 32) & Right (Repeat ('0', 32) & Num (Num# (Right ($1, 8), '(HEX)'), '(BIN)'), 32), 52), 40, 1)) / pow (2, 40) +
         Num (Mid (Right (Right (Repeat ('0', 32) & Num (Num# (Left ($1, 8), '(HEX)'), '(BIN)'), 32) & Right (Repeat ('0', 32) & Num (Num# (Right ($1, 8), '(HEX)'), '(BIN)'), 32), 52), 41, 1)) / pow (2, 41) +
         Num (Mid (Right (Right (Repeat ('0', 32) & Num (Num# (Left ($1, 8), '(HEX)'), '(BIN)'), 32) & Right (Repeat ('0', 32) & Num (Num# (Right ($1, 8), '(HEX)'), '(BIN)'), 32), 52), 42, 1)) / pow (2, 42) +
         Num (Mid (Right (Right (Repeat ('0', 32) & Num (Num# (Left ($1, 8), '(HEX)'), '(BIN)'), 32) & Right (Repeat ('0', 32) & Num (Num# (Right ($1, 8), '(HEX)'), '(BIN)'), 32), 52), 43, 1)) / pow (2, 43) +
         Num (Mid (Right (Right (Repeat ('0', 32) & Num (Num# (Left ($1, 8), '(HEX)'), '(BIN)'), 32) & Right (Repeat ('0', 32) & Num (Num# (Right ($1, 8), '(HEX)'), '(BIN)'), 32), 52), 44, 1)) / pow (2, 44) +
         Num (Mid (Right (Right (Repeat ('0', 32) & Num (Num# (Left ($1, 8), '(HEX)'), '(BIN)'), 32) & Right (Repeat ('0', 32) & Num (Num# (Right ($1, 8), '(HEX)'), '(BIN)'), 32), 52), 45, 1)) / pow (2, 45) +
         Num (Mid (Right (Right (Repeat ('0', 32) & Num (Num# (Left ($1, 8), '(HEX)'), '(BIN)'), 32) & Right (Repeat ('0', 32) & Num (Num# (Right ($1, 8), '(HEX)'), '(BIN)'), 32), 52), 46, 1)) / pow (2, 46) +
         Num (Mid (Right (Right (Repeat ('0', 32) & Num (Num# (Left ($1, 8), '(HEX)'), '(BIN)'), 32) & Right (Repeat ('0', 32) & Num (Num# (Right ($1, 8), '(HEX)'), '(BIN)'), 32), 52), 47, 1)) / pow (2, 47) +
         Num (Mid (Right (Right (Repeat ('0', 32) & Num (Num# (Left ($1, 8), '(HEX)'), '(BIN)'), 32) & Right (Repeat ('0', 32) & Num (Num# (Right ($1, 8), '(HEX)'), '(BIN)'), 32), 52), 48, 1)) / pow (2, 48) +
         Num (Mid (Right (Right (Repeat ('0', 32) & Num (Num# (Left ($1, 8), '(HEX)'), '(BIN)'), 32) & Right (Repeat ('0', 32) & Num (Num# (Right ($1, 8), '(HEX)'), '(BIN)'), 32), 52), 49, 1)) / pow (2, 49) +
         Num (Mid (Right (Right (Repeat ('0', 32) & Num (Num# (Left ($1, 8), '(HEX)'), '(BIN)'), 32) & Right (Repeat ('0', 32) & Num (Num# (Right ($1, 8), '(HEX)'), '(BIN)'), 32), 52), 50, 1)) / pow (2, 50) +
         Num (Mid (Right (Right (Repeat ('0', 32) & Num (Num# (Left ($1, 8), '(HEX)'), '(BIN)'), 32) & Right (Repeat ('0', 32) & Num (Num# (Right ($1, 8), '(HEX)'), '(BIN)'), 32), 52), 51, 1)) / pow (2, 51) +
         Num (Mid (Right (Right (Repeat ('0', 32) & Num (Num# (Left ($1, 8), '(HEX)'), '(BIN)'), 32) & Right (Repeat ('0', 32) & Num (Num# (Right ($1, 8), '(HEX)'), '(BIN)'), 32), 52), 52, 1)) / pow (2, 52)
        ) * pow (2, Num (Num# (Mid (Right (Repeat ('0', 32) & Num (Num# (Left ($1, 8), '(HEX)'), '(BIN)'), 32) & Right (Repeat ('0', 32) & Num (Num# (Right ($1, 8), '(HEX)'), '(BIN)'), 32), 2, 11), '(BIN)'), '(DEC)') - 1023)
          * If (Left (Right (Repeat ('0', 32) & Num (Num# (Left ($1, 8), '(HEX)'), '(BIN)'), 32) & Right (Repeat ('0', 32) & Num (Num# (Right ($1, 8), '(HEX)'), '(BIN)'), 32), 1) = '1', -1, 1)
      , If (Num (Num# (Mid (Right (Repeat ('0', 32) & Num (Num# (Left ($1, 8), '(HEX)'), '(BIN)'), 32) & Right (Repeat ('0', 32) & Num (Num# (Right ($1, 8), '(HEX)'), '(BIN)'), 32), 2, 11), '(BIN)'), '(DEC)') = 0
            and SubStringCount (Right (Right (Repeat ('0', 32) & Num (Num# (Left ($1, 8), '(HEX)'), '(BIN)'), 32) & Right (Repeat ('0', 32) & Num (Num# (Right ($1, 8), '(HEX)'), '(BIN)'), 32), 52), '0') = 52
          , 0
          , Null ()
           )
       )
   )";

It can be used as:

Date($(DecryptHex(FieldName)))
59 Views
0 Likes