topic Re: compare value in two chart in QlikView

compare value in two chart

realpixel — Mon, 30 Mar 2015 16:14:34 GMT

Hello,

From SQL database I have a chart with ID, name, and firstname, and in an another chart from Excel file I have ID, name1 and firstname1.

I want to have in a new chart the value present (ID, name, firstname) in excel file but not on the sql database, someone I have an idea how to do?

Re: compare value in two chart

maxgro — Mon, 30 Mar 2015 16:25:36 GMT

if you join (in load) the 2 source, excel and sql, you get one table with all the fields

then with a resident load you can flag record in sql not in excel, in excel not in sql, etc......

Re: compare value in two chart

Mon, 30 Mar 2015 16:55:43 GMT

Hi,

If you have SQL and excel in different tables linked by ID, you can create a chart with dimension:ID and expressions:

=if(isnull(name),name1)

=if(isnull(name),firstname1)

to list only the ID that are missing in the SQL table (=name is null).

Re: compare value in two chart

realpixel — Fri, 03 Apr 2015 09:58:34 GMT

Hello,

Thank you for reply but I have always a problem.

You can find attached a part of my QV file, I replaced Excel file by inline table named (ExtractExcelFiles).

When I done a selection by service (ATELIERS) and by years, month, and day I need to have the difference between tables "Table" (matricule, nom, prenom) and table "ExtractExcelFiles" (matricule, NOMS1, PRENOMS1)

For example for date 24/03/2015:

- On chart "LISTE PRESENTS" I have 53 present employees, the total of employees present in "ExtractExcelFiles" table is 91

- Now on Chart "LISTE ABSENTS" in need to have the difference of total employees and "LISTES PRESENTS" : 91 - 53 = 38 whith matricule, NOMS1 and PRENOMS.

I dont know how to do, mayde someone can help me.

Re: compare value in two chart

Sun, 05 Apr 2015 21:57:15 GMT

Hi,

By selecting dates you effectively exclude the persons you want in "LISTE ABSENTS", so you want to ignore all date selections:

{$<Année=,Mois=,CalendarWeek=,CalendarDay=,

Then matricule must not be in a list of matricule1s. In this set you want to take into account your date selections:

matricule = {'*'} -{$(=concat({$}distinct matricule1,','))}>}

Try it out. Hope it's what you are looking for.

=Count({$<Année=,Mois=,CalendarWeek=,CalendarDay=, matricule = {'*'} -{$(=concat({$}distinct matricule1,','))}>}NOMS1)

Re: compare value in two chart

realpixel — Tue, 07 Apr 2015 10:56:53 GMT

Great Thank you t h for your assistance.