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
Valued Contributor

Mapping

i have a confusing problem..please suggest to get results, 

1> select "Asset Tag" from tableA with type='windows 2003'
2>match them with tableB "name"
3>match the result(common names between table A &B with type = 'windows 2003') with table C (nameS)

table A:                                                      

 

Asset Tagtype
awindowns 2008
bwindows 2003
cwindows 2003
dwindows 2012
ewindows 2012
fwindows 2003
gwindows 2003
hwindows 2008
iwindowns 2001
jwindows 2012

table B:    

 

nameversion
b6.1
c6.2
d6.3
e6.1
f6.2
g6.2
j6.1
l6.3
k6.2
m6.2

table C:

 

nameS
a
b
c
d
e
f
x
y
z
w

<<<<<<<<MY SCRIPT>>>>>>>>

map_EZV:
Mapping LOAD
upper("Asset Tag"),
'Exists in EZV'
FROM [lib://Server/EZV_Win_server.xlsx]
(ooxml, embedded labels, table is EZV_Win_server);

map_AD:
Mapping LOAD
upper(Name),
'Exists in AD'
FROM [lib://Server/AD.csv]
(txt, codepage is 28591, embedded labels, delimiter is '\t', msq);

map_BACKUP:
Mapping LOAD
Upper(nameS),
'Exist in BACKUP'
FROM [lib://Server/Backup_Report.csv]
(txt, codepage is 28591, no labels, delimiter is '\t', msq);


EZV:
LOAD
Upper("Asset Tag") as Server_Name,
"Network Identifier",
"Type (Full)",
Model,
"Location (Full)",
"Main Usage",
"[E_BACKUP_TYPE]",
Backup_Asset_Tag,
"[E_ENVIRONMENT]",
"[E_PRA]",
"[E_SOLUTION]",
ApplyMap('map_AD',Upper("Asset Tag"),'Not Exist in AD') as "AD Exists Flag",
  ApplyMap('map_BACKUP',Upper("Asset Tag"),'Not Exist in BACKUP') as "BACKUP OR NOT"


FROM [lib://Server/EZV_Win_server.xlsx]
(ooxml, embedded labels, table is EZV_Win_server);

AD:
LOAD
Upper(Name) as Server_Name,
OperatingSystem,
OperatingSystemVersion,
CanonicalName,
  ApplyMap('map_EZV',Upper(Name),'Not Exist in EZV') as "EZV Exists Flag",
  ApplyMap('map_BACKUP',Upper("Name"),'AD Server Not Exist in BACKUP') as "AD_BACKUP"

FROM [lib://Server/AD.csv]
(txt, codepage is 28591, embedded labels, delimiter is '\t', msq);

BackupReport:
LOAD
Upper(nameS) as Server_Name ,
  ApplyMap('map_EZV',Upper(nameS),'Not Exist in EZV') as "BACKUP Exists Flag",
  ApplyMap('map_BACKUP',Upper(nameS),'NotBackuped') as "BACKUPED"


FROM [lib://Server/Backup_Report.csv]
(txt, codepage is 28591, no labels, delimiter is '\t', msq);

3 Replies

Re: Mapping

try like this

TableA:

LOAD AssetTag,

Type

from

where Type='Windows 2003';


TableB:

LOAD *

where Exists(AssetTag,name);


TableC:

LOAD

*

where Exists(name,nameS);



hope this gives you the desired results

agrimroquette
Valued Contributor

Re: Mapping

Thanks a lot for reply, this where condition that you suggested is intelligent but it will affect whole data, but i want to show like this condition.PNG

Re: Mapping

did not get you ..could you explain with an example ? so that I could write the logic accordingly