topic Re: Scripting - addressing the gap in QlikView

Scripting - addressing the gap

Fri, 21 Feb 2014 21:53:07 GMT

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

Re: Scripting - addressing the gap

Sat, 22 Feb 2014 06:09:10 GMT

try the following code

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

Re: Scripting - addressing the gap

maxgro — Sat, 22 Feb 2014 08:29:27 GMT

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

Re: Scripting - addressing the gap

sundarakumar — Sat, 22 Feb 2014 08:50:54 GMT

Hi,

PFA.

-Sundar

Re: Scripting - addressing the gap

maxgro — Sat, 22 Feb 2014 09:44:13 GMT

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;

Re: Scripting - addressing the gap

Mon, 24 Feb 2014 16:41:49 GMT

Thank you so much for your help.

Jasmine

Re: Scripting - addressing the gap

Mon, 24 Feb 2014 16:44:34 GMT

Hi Massimo,

Much appreciate your detailed explaination, it is very helpful.

Jasmine