Users Data not linking

[Manni_SM]

Hi All, @rubenmarin 

in  operation monitor dashboard all data is restricted but not in user table data is not looking  due to data not in sync . 

my tables is lined with userid  with different format    like some text and right side   below is data 

Table1   ,                      Table 2

userID                             User ID 

hbap\245362                    3452648

hbeu\352727                    5375828

        563836                     hbap\345274

        453739                     hbeu\546896

i tried this but not working 

num(SubField(userId,'\',2)) as UserId,

[Lisa_P]

Depending on the length of your values, try this:

If(len(UserId)> 8, num(SubField(UserId,'\',2)), UserId) as UserId,

[rubenmarin]

Hi, 2 comments:

- Subfield with '2' as parameter will return null() for those values without a '\', you can use -1 to get the last, which also works for values without '\'. You can also use Coalesce() as an 'else' to try differnt options until one returns a non-null value

- Num() is used to format a number, Num#() is used to convert text to number:

You can try with one of those:

- num(num#(SubField(userId,'\',-1),'0')) as UserId,

- num(num#(Coalesce(SubField(userId,'\',2),userId),'0')) as UserId,

Or convert the values to text:

- Text(SubField(userId,'\',-1)) as UserId,

[Manni_SM]

Hi @rubenmarin  thank you i tried and it works well..but now issue is my logcontent table has 25distinct values which is correct

now in below tables its showing  164 which is wrong..   i tried inner join with logcontent but it didnt work.

// inner join(logcontent)

RegMap_Entitlement:

LOAD

Date(SNAP_DT,'DD/MM/YYYY') as [Report Date],

 Text(SubField(EMPLY_NUM,'\',-1)) as UserId_Key,

   EMPLY_FULL_NAME,

    EMPLY_EMAIL_ADDR_TEXT,

    If(ENTL_JRIS_CDE='ALL',ENTL_JRIS_CDE,ApplyMap('MAP_JRIS',ENTL_JRIS_CDE,'Jurisdiction not found')) as ENTL_JRIS_NAME,

    ENTL_RISK_STWRD_AREA_TEXT,

       DEPT_NAME,

FROM [$(vQVD_Root_Dir)\DTL_SNAP.qvd](qvd);

[rubenmarin]

Hi, you can create a table with UserId_Key and some other column from each of the other 3 tables where this field is loaded, like UserQlikID,UserID and ReportDate (it will be better with fields with 100% density).

This way you can check from where are loading the unexpected values, most probably from users that don't have any login.

By the tables name I see, the inner join should be: inner join(LogContent)

[Manni_SM]

Hi @rubenmarin   now data looks near  but i found some non matching records from  LogConent Table  

so i am trying to remove those   used below code  but getting  " LogContent table not found  error  " am i missing anything?

Rename Table LogContent to LogContent_Prep;

Left JOin(LogContent_Prep)
Load
_proxySessionPackage,
'Keep' as Records_to_Keep
Resident SessionSummary;

LogContent:
Load
*
Resident LogContent_Prep
Where Records_to_Keep = 'Keep';

Drop Table LogContent_Prep;

[rubenmarin]

Hi, if you load a table with the same fields as the previous one, the tables will be autoconcatenated, so only the LogContent_Prep table will exists on the model, to aovid this you can use NoConcatenate:

LogContent:
NoConcatenate
Load
*
Resident LogContent_Prep
Where Records_to_Keep = 'Keep';

[Manni_SM]

Thanks @rubenmarin  it worked .  And  almost all looks ok except  Activeuserbucket count 

i have fact table(LogContent) with 11 distinct   but in bucket table  5 only showing .. 

Logcontent table:

user session final table with 11

but in Activeuserbucket table showing 5:

logic for activeuserbucket group

ActiveuserGroupBucket:
LOAD
Text(SubField(UserId,'\',-1)) as UserId_Key,

If(Count([_proxySessionPackage])<=2

,'Infrequent',

If(Count([_proxySessionPackage])<=5

,'Regular',

If(Count([_proxySessionPackage])<=10

,'Enthusiast',

If(Count([_proxySessionPackage])<=20,

'Motivated','HiglyMotivated')))) as ActiveuserGroupBucket

Resident LogContent

Where floor(num(LogEntryPeriodStart))>=Floor(Today()-30)

group by UserId;

[rubenmarin]

Hi, could it be because ActiveuserGroupBucket is loading logins in last 30 days? The other users could be active but without a login in the last 30 days.

You can check it in front end, adding a table with UserId_Key as dimension and Today()-Floor(Max(LogEntryPeriodStart)) as expression

[Manni_SM]

Hi @rubenmarin   i tried above expression its showing 11