topic Re: How to Check if value exists with date in QlikView

How to Check if value exists with date

DipeshVadgama — Tue, 13 Dec 2016 07:55:54 GMT

Dear All,

I am trying to find if Current week "Number" exist in pervious week "Number".

I try to use Exists & match but no use.

Dat:

LOAD [Date],

[Number],

Match(if([Date]>Today()-6,[Number]),if([Date]<=Today()-6,[Number])) as "Repeated"

FROM [lib://Dat.xlsx]

(ooxml, embedded labels, table is Sheet2);

Re: How to Check if value exists with date

marcus_sommer — Tue, 13 Dec 2016 07:59:42 GMT

The previous loaded records could you check with Peek() or Previous() ? maybe in this way:

...

if(Number = previous(Number), 'then', 'else') as Check

...

- Marcus

Re: How to Check if value exists with date

vinieme12 — Tue, 13 Dec 2016 09:21:04 GMT

Need to understand the scenario here

what if the "Number" exist in previous week and also the week before what then?

What are you trying to achieve with this flag?

Re: How to Check if value exists with date

DipeshVadgama — Tue, 13 Dec 2016 09:34:20 GMT

Hi Vineeth,

I trying to find weather current week numbers exist in past or not.

Re: How to Check if value exists with date

vinieme12 — Tue, 13 Dec 2016 09:36:34 GMT

if you want to see anywhere in the past and not specifically a set period in the past then use Autonumber()

RepeatCount:

LOAD Date,

Number,

AutoNumber(Number&Date,Number) as RepeatedCount

FROM

(ooxml, embedded labels, table is Sheet2);

Any Number that has RepeatedCount > 1 is a repeat.

Re: How to Check if value exists with date

Gysbert_Wassenaar — Tue, 13 Dec 2016 09:38:31 GMT

Perhaps like this:

LOAD

Number,

if(Exist(Number), 'We've seen this number before', 'Never seen it before') as DidThisNumberExistAlready,

...other fields

FROM

....source data....

;

Re: How to Check if value exists with date

DipeshVadgama — Tue, 13 Dec 2016 11:32:55 GMT

I hope this attachment will help.

Same I am trying to do it in Qlik Sense editor

Applied below rules, but not exact results.

if([Date]>Today()-6,if(Exists([Number]), 'We have seen this number before', 'Never seen it before')) as Flag1,

if(match(if([Date]>Today()-6,[Number]),if([Date]<>Today()-6,[Number]))>0,'y','n') as Flag2,

if(([Date]>Today()-6),if(match([Number],(if([Date]<=Today()-6,[Number])))>0,'y','n'),'No') As Flag3,

AutoNumber(Number&Date,Number) as Flag4

Re: How to Check if value exists with date

nitin_01 — Tue, 13 Dec 2016 11:47:51 GMT

Hi Dipesh,

Hope this helps :

Post_1:
LOAD Distinct
     Date as post_date,
     Number,
     '1' as junk
FROM

(ooxml, embedded labels, table is Sheet2);

Post:
LOAD post_date,
     Number,
     if(Number=Peek(Number),'Exist','Non-Exist') as Check
    Resident Post_1
    Order By Number;


DROP Field 'junk' From 'Post_1';

DROP Table Post_1;

Regards,

Nitin.

Re: How to Check if value exists with date

vinieme12 — Tue, 13 Dec 2016 12:04:02 GMT

small change here

Post:

LOAD post_date,

Number,

if(Number=Peek(Number),'Exist','Non-Exist') as Check

Resident Post_1

Order By Number,post_date;

Re: How to Check if value exists with date

vinieme12 — Tue, 13 Dec 2016 12:07:26 GMT

TRY

RepeatCount:

LOAD Date,

Number,

if(AutoNumber(Number&Date,Number) > 1,'REPEAT','NEW') as Flag4

FROM

(ooxml, embedded labels, table is Sheet2);

Re: How to Check if value exists with date

nitin_01 — Tue, 13 Dec 2016 12:11:09 GMT

Your right Vineeth, I missed it.

Regards,

Nitin