Scripting - addressing the gap

[#1]

Hi Qlikviewers,

I need help on writing a statement which to show countries have data on 11/30/2013 but don't have data on 12/31/2013, a chart which only showing countries reported on 11/30/2013 and no data on 12/31/2013.

Here's the data and highlighted in blue are countries have no data on 12/31/2013. 

Country

Date

filed

Spain	11/30/2013 0:00:00	10
Indonesia	11/30/2013 0:00:00	4
Indonesia	12/31/2013 0:00:00	3
Germany	11/30/2013 0:00:00	6
Guatemala	11/30/2013 0:00:00	4
Guatemala	12/31/2013 0:00:00	1
Philippines	11/30/2013 0:00:00	3
Philippines	12/31/2013 0:00:00	1
Peru	12/31/2013 0:00:00	3
Honduras	11/30/2013 0:00:00	3
Tanzania	11/30/2013 0:00:00	2
Jamaica	12/31/2013 0:00:00	2
Nicaragua	11/30/2013 0:00:00	1
Nicaragua	12/31/2013 0:00:00	1
Egypt	11/30/2013 0:00:00	1
Egypt	12/31/2013 0:00:00	1
Guam	11/30/2013 0:00:00	2
Romania	11/30/2013 0:00:00	2
Thailand	12/31/2013 0:00:00	1
Bahrain	12/31/2013 0:00:00	1
Israel	11/30/2013 0:00:00	1
Greece	12/31/2013 0:00:00	1
Puerto Rico	12/31/2013 0:00:00	1
Nigeria	11/30/2013 0:00:00	1

Thank you in advance for your help.

Have a great weekend.

Jasmine

[#2]

try the following code

=If(Date='11/30/2013' and Data<> '-' and Date='11/31/2013' And Data='-',Country)

[maxgro · Accepted solution]

you can use a chart with

dimension          Country

expression          if(count({$<[Date]={'11/30/2013'}>} Filed) >0 and count({$<[Date]={'12/31/2013'}>} Filed) =0, 'X')

Country	Yes 11, No 12
Germany	X
Guam	X
Honduras	X
Israel	X
Nigeria	X
Romania	X
Spain	X
Tanzania	X

[sundarakumar]

Hi,

PFA.

-Sundar

[maxgro]

or if you prefere in script:

I've added a table (bold) at your data

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='€ #.##0,00;-€ #.##0,00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='MM/DD/YYYY';

SET TimestampFormat='MM/DD/YYYY hh:mm:ss[.fff]';

SET MonthNames='gen;feb;mar;apr;mag;giu;lug;ago;set;ott;nov;dic';

SET DayNames='lun;mar;mer;gio;ven;sab;dom';

DIRECTORY;

Source:

LOAD @1 as Country,

     date(date#(left(@2, 10)))  as Date,

     @3 as Filed

FROM

[http://community.qlik.com/thread/108296]

(html, codepage is 1252, no labels, table is @1);

SourceByCountry:

LOAD

  Country,

  if(count(distinct Date)=1 and Min(Date) = '11/30/2013', 1, 0) as [Flag  11/12]

Resident

  Source

Group By Country;

[#6]

Thank you so much for your help.

Jasmine

[#7]

Hi Massimo,

Much appreciate your detailed explaination, it is very helpful.

Jasmine