Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
DipeshVadgama
Partner - Creator II
Partner - Creator II

How to Check if value exists with date

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

10 Replies
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
Champion III
Champion III

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?

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
DipeshVadgama
Partner - Creator II
Partner - Creator II
Author

Hi Vineeth,

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

vinieme12
Champion III
Champion III

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.

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
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....

     ;



talk is cheap, supply exceeds demand
DipeshVadgama
Partner - Creator II
Partner - Creator II
Author

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

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
Champion III
Champion III

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;

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
vinieme12
Champion III
Champion III

TRY

RepeatCount:

LOAD Date,

     Number,

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

FROM

(ooxml, embedded labels, table is Sheet2);

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.