Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

agrimroquette
Contributor II

check missing values

Hi,

I have an excel file with two sheets, i want to get those values which are not common in both the field,

where they are case sensitive,

  eg

Assetname , Item

        YOu    , you

          My    , my

my script is-

LOAD

    Assetname,

    Statename,

    Lastchanged

FROM [lib://t1/web40reppartofdomain1.xls]

(biff, embedded labels, table is report$);

LOAD

    Catégorie,

    Item,

    "Date d'installation"

FROM [lib://t1/web40reppartofdomain1.xls]

(biff, embedded labels, table is Feuil1$);

i want to match Item and Assetname and get those values which are not common in both of them.

and want an output as "Yes-matched all" & "No-All not matched". in these sheets there is nothing common(Key) but there is date fields in both of them

1 Solution

Accepted Solutions
vinieme12
Esteemed Contributor II

Re: check missing values

this

map_report:

Mapping LOAD upper(Assetname) as Assetname,

  'Exists in Report'

FROM

(biff, embedded labels, table is report$);

map_Feuil1:

Mapping LOAD

    upper(Item) as ITEM,

    'Exists in Feuil1'

FROM

(biff, embedded labels, table is Feuil1$);

Tab_Feuil1:

LOAD Catégorie,

    Item,

    [Date d'installation],

    ApplyMap('map_report',upper(Item),'Not Exist in Report') as ReportExistsFlag

FROM

(biff, embedded labels, table is Feuil1$);

LOAD Assetname,

    Statename,

    Lastchanged,

          ApplyMap('map_Feuil1',upper(Assetname),'Not Exist in Feuil1') as Feuil1ExistsFlag

FROM

(biff, embedded labels, table is report$);

2 Replies
ahaahaaha
Honored Contributor

Re: check missing values

Hi Agrim,

As variant, you can use Set Analisys expression to work similarly to the island of data. In attached file I gave an example of selecting only the same records in fields [Assetname] and [Item] (Table 3) and the same records selection with the state of field Statename as 'Non-active' (Table 4).

Similar to this approach, you can make the other selections you need.

Regards,

Andrey

vinieme12
Esteemed Contributor II

Re: check missing values

this

map_report:

Mapping LOAD upper(Assetname) as Assetname,

  'Exists in Report'

FROM

(biff, embedded labels, table is report$);

map_Feuil1:

Mapping LOAD

    upper(Item) as ITEM,

    'Exists in Feuil1'

FROM

(biff, embedded labels, table is Feuil1$);

Tab_Feuil1:

LOAD Catégorie,

    Item,

    [Date d'installation],

    ApplyMap('map_report',upper(Item),'Not Exist in Report') as ReportExistsFlag

FROM

(biff, embedded labels, table is Feuil1$);

LOAD Assetname,

    Statename,

    Lastchanged,

          ApplyMap('map_Feuil1',upper(Assetname),'Not Exist in Feuil1') as Feuil1ExistsFlag

FROM

(biff, embedded labels, table is report$);