Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear Friends,
I am stuck in my data, appreciate if any one can help me.
I have a data with ID, Day.
I what to know which id was present today & also yesterday then Trigger or flag it in other field as Yes.
and to others as No (ID was present today but not yesterday or ID was present yesterday but not today)
Eg.
ID | Day | Trigger | |
ID12345 | Yesterday | ||
ID12346 | |||
ID12347 | Yesterday | ||
ID12348 | |||
ID12349 | Yesterday | ||
ID12350 | Yesterday | ||
ID12351 | Yesterday | ||
ID12352 | |||
ID12353 | Yesterday | ||
ID12354 | Yesterday | ||
ID12355 | |||
ID12345 | Today | Yes | Trigger this as Id was present today and yesterday |
ID12346 | Today | ||
ID12347 | |||
ID12348 | Today | ||
ID12349 | Today | Yes | Trigger this as Id was present today and yesterday |
ID12350 | Today | Yes | Trigger this as Id was present today and yesterday |
ID12351 | |||
ID12352 | Today | ||
ID12353 | |||
ID12354 | Today | Yes | Trigger this as Id was present today and yesterday |
ID12355 | Today |
Try:
Load
ID
Day
Resident Table;
Left join
Load Distinct
ID,
if(wildmatch(Day,'Yesterday') and wildmatch(Day,'Today'),1,0) as Trigger
Resident Table;
//Try this
Table1:
LOAD * FROM [Data.qvd];
Noconcatenate
Table2:
LOAD * Resident Table1
order by ID, Day desc;
// desc makes yesterday above today
Drop Table Table1;
Noconcatenate
Table2:
LOAD
*,
if(ID=peek(ID) and Day='Today' and peek(Day)='Yesterday','Yes','No') as Trigger
Resident Table2;
Drop Table Table2;
Source:
LOAD ID,
Day,
Trigger
FROM [https://community.qlik.com/thread/242957] (html, codepage is 1252, embedded labels, table is @1);
Final:
LOAD
*,
if(ID=Peek('ID') and Day='Today' and Peek('Day')='Yesterday', 'Yes', 'No') as Flag
RESIDENT Source
ORDER BY ID, Day DESC;
DROP Table Source;
Hi Dipesh,
You can easily achieve this by using Mapping Load, try below script
YesterdayMapping:
MAPPING LOAD
ID,
Day
FROM DataSource
WHERE Day = 'Yesterday';
Data:
LOAD
ID,
Day,
If(Day = 'Today' AND ApplyMap('YesterdayMapping', ID, 'N/A') = 'Yesterday', 'Yes') AS Trigger
FROM DataSource;
Hope this helps you.
Regards,
Jagan.
Hi Dipesh,
If you got the answer close this thread by giving Correct and Helpful answers to the posts which helps you in finding the answers.
Regards,
Jagan.
Hey guys thanks for your help. But I realized this is not helping my script.
I have attached excel file, hope this will explain which I want same in qlik sense.