How to Check if value exists with date

[DipeshVadgama]

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);

[marcus_sommer]

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

...

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

...

- Marcus

[vinieme12]

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?

[DipeshVadgama]

Hi Vineeth,

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

[vinieme12]

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.

[Gysbert_Wassenaar]

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....

     ;

[DipeshVadgama]

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

[nitin_01]

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.

[vinieme12]

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;

[vinieme12]

TRY

RepeatCount:

LOAD Date,

     Number,

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

FROM

(ooxml, embedded labels, table is Sheet2);