topic Re: getting duplicates in QlikView

getting duplicates

Thu, 27 Nov 2014 06:34:21 GMT

hi team

i am getting duplicates when i show the no of sessions.please find my script

left join(Meta)

LOAD

[LDAP Group],

[LDAP Unix ID] as [Unix ID]

Resident User;

User:

NoConcatenate

LOAD

MapSubstring ('map1', LDAP_grp) as LDAP_grp,

ldap_usr as ldap_usr

Resident User_temp;

Meta:

LOAD

left(filename(),Index(filename(),'.Meta')-1) as Dash,

@1:n as data,

lower(SubField(@1:n,'gene\')) as LDAP_GRP

FROM

[$(vSharedIncomingFlatFiles)Shared\UserFiles\Usage\.meta files\*.qvw.Meta]

(fix, codepage is 437, no eof);

Re: getting duplicates

tresB — Thu, 27 Nov 2014 06:50:04 GMT

If you are trying to join on a specific field, in qv the field names from both tables have to be exactly same, otherwise, without any field in common, cross-join is implemented implicitly. Hence, probably you should try like:

...

Meta:

LOAD

left(filename(),Index(filename(),'.Meta')-1) as Dash,

@1:n as data,

lower(SubField(@1:n,'gene\')) as LDAP_GRP [LDAP Group]

...

Re: getting duplicates

Thu, 27 Nov 2014 06:54:48 GMT

but we have below table also

Meta_temp:

NoConcatenate

LOAD

Dash as Dashboard,

MapSubstring ('Map1', LDAP_GRP) as [LDAPGroup]

Resident Meta;

Re: getting duplicates

tresB — Thu, 27 Nov 2014 07:00:53 GMT

Using NoConcatenate, and probably you are droping this temp table later. If so, this table should not be a matter of concern.

Re: getting duplicates

Thu, 27 Nov 2014 07:01:03 GMT

here is my entire script

/*V1.2 Read all the meta files and for each dashboard, get the LDAP Groups that have access access to the dashboard*/

Meta:

LOAD

left(filename(),Index(filename(),'.Meta')-1) as Dash,

@1:n as data,

lower(SubField(@1:n,'gene\')) as LDAP_GRP

FROM

[$(vSharedIncomingFlatFiles)Shared\UserFiles\Usage\.meta files\*.qvw.Meta]

(fix, codepage is 437, no eof);

Map1:

Mapping LOAD

lower(@1), Lower(@1) &';'

FROM

[$(vSharedIncomingFlatFiles)Shared\UserFiles\Usage\Ldapgrpcount.txt]

(txt, codepage is 1252, no labels, delimiter is ':', msq);

Meta_temp:

NoConcatenate

LOAD

Dash as Dashboard,

MapSubstring ('Map1', LDAP_GRP) as [LDAPGroup]

Resident Meta;

DROP Table Meta;

RENAME Table Meta_temp to Meta;

Meta_temp:

NoConcatenate

LOAD

Dashboard,

left([LDAPGroup],Index([LDAPGroup],';')-1) as [LDAPGroup]

Resident Meta;

DROP Table Meta;

RENAME Table Meta_temp to Meta;

Meta_temp:

NoConcatenate

LOAD

Dashboard,

[LDAPGroup] as LDAP_Grp

Resident Meta

Where not IsNull([LDAPGroup]);

DROP Table Meta;

RENAME Table Meta_temp to Meta;

ldap_groups:

LOAD lower(@1) as LDAP_Grp,

@2 as count

FROM

[$(vSharedIncomingFlatFiles)Shared\UserFiles\Usage\Ldapgrpcount.txt]

(txt, codepage is 1252, no labels, delimiter is ':', msq);

Meta_temp1:

Left Keep(ldap_groups)

LOAD

lower(Dashboard) as Dashboard_App,

LDAP_Grp

Resident Meta;

DROP table Meta;

RENAME Table Meta_temp1 to Meta;

DROP Table ldap_groups;

//sundars4- added "_" and "-" strings to replaced by null as part of gSR-1267

Map2:

mapping load * inline [

F1, F2

qlikview_,

portal_,

_,' '

-,' '

];

//sundars4- removed SOIM group as part of gSR-1267

Meta_temp:

NoConcatenate

LOAD

upper(subfield(Dashboard_App,'.qvw',1)) as 'Dashboard App',

upper(MapSubstring ('Map2', LDAP_Grp)) as [LDAP Group]

Resident Meta

where LDAP_Grp <> 'qlikview_admin' and upper(LDAP_Grp) <> 'SOIM' and upper(LDAP_Grp) <> 'COMOPS SOIM';

DROP Table Meta;

RENAME Table Meta_temp to Meta;