Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
Please help me with the below scenario where am facing the issue. I have data for months(January, February, March . etc.,) In the below example I provided data for 2 months (March and April).
Id |
Month-Year |
Stage |
1 |
March |
Stage 1 |
2 |
March |
Stage 3 |
3 |
March |
Stage 2 |
Id |
Month-Year |
Stage |
1 |
April |
Stage 1 |
2 |
April |
Stage 2 |
3 |
April |
Stage 3 |
I want to compare the Id's row by row for April vs March.(compare April as latest and March as old data)
Scenario 1 : Id 1 related Stage value is same in April and March so the value should be "Match"
Scenario 2 : Id 2 in March its Stage value is "Stage 3" where as in April its "Stage 2", so its demoted so the value to be "Down"
Scenario 3 : Id 3 in March it Stage value is "Stage 2" where as in April its "Stage 3" so its promoted so the value to be "Up".
Final Result to be as below :
Id |
Expected Result |
1 |
Match |
2 |
Down |
3 |
Up |
Any help is helpful !.
Thanks
the if statement I used is the flag, i tried the data you have shown as Inline Data,
here 45016 is the numerical value of month-end of march 2023 and 45046 for april 2023
This is the table formed, all are respective dimensions and no measures.
If you want to Show stage as Stage-1, Stage -2 etc , then either use concat in UI or create a new field where you keep the real name, and same for your Month-Year Column.
You have just showed your if statement and that seems correct the only error can be that you havent ordered your data, but 9 ifs and every if having an AND condition will make the load script slower.
to get last month's value (it's long because I've tried to have Dec last year to compare with Jan this year.)
If(Year(Today())>Year(Monthstart(AddMonths(Today(),-1))),
({$YourMONTHfield<={"12"},YourYEARfield={"$(=num(year(today())-1))"}>}Stage),({$<YourMONTHfield={"$(=num(month(today()))-1)"},YourYEARfield="$(=num(year(today())))"}>}Stage)
then it would be comparing with the current month and if statement based on how the value in 'stage' moved between the two.
Doing it in load Script will be much easier,
i hope all data is in same table, but if it is in different tables as the data structure is same you can just concatenate all the table.
And i also hope that you have a datefield, rather than just month, if you dont have dates, just have a month and year column, you must create a datefield either monthstart date or monthend date(you can search it and if you dont understand that i will tell), i am considering monthstart as datefile
Source:
Load id,Monthstart,keepchar(Stage,'1234567890') as Stage from Source(Concatenate all data or is a single table upto you);
noconcatenate
A:
Load *, If(id = previous(id),If(Stage=previous(Stage),'MATCH',If(Stage<previous(Stage),'DOWN',If(Stage>previous(Stage),'UP',Null()))),null());
load * resident Source order by id asc, monthstart asc;
Drop Table Source;
Hi Akash,
Thanks for taking time into this.
I am actually looking for a Flag in script where we cane have 3 values like (Match,UP,DOWN).
I have my Id's and Stage in different tables but I tried as you suggested to concatenate the 2 tables.
The logic you suggested is not working as I don't see the Flag values as expected.
I see may be similar to below works but I am unable to implement (my assumption)
if(Previous Month Stage Id = 1 and Current Month Stage ID = 1, 'Match',
if(Previous Month Stage Id = 2 and Current Month Stage ID = 2, 'Match',
if(Previous Month Stage Id = 3 and Current Month Stage ID = 3, 'Match',
if(Previous Month Stage Id = 1 and Current Month Stage ID = 2, 'Up',
if(Previous Month Stage Id = 1 and Current Month Stage ID = 3, 'Up',
if(Previous Month Stage Id = 2 and Current Month Stage ID = 3, 'Up',
if(Previous Month Stage Id = 1 and Current Month Stage ID = 2, 'Down',
if(Previous Month Stage Id = 2 and Current Month Stage ID = 3, 'Down',
if(Previous Month Stage Id = 3 and Current Month Stage ID = 1, 'Down'))))))))) as Flag
Am thinking this will work so that I can use that "Flag" field where ever I need to show only "Up" related records or "Down" accordingly in my Set Analysis.
Thanks,
Sahi
Hello G3S,
Its not particularly to get the last month value, its like comparing last month vs the before month.
April vs March :
If in March its Stage 2 and in April its Stage 3 then its Up,
If in March its Stage 1 and April its Stage 2 then its Up,
If in March its Stage 1 and April its Stage 3 then its Up
If in April its Stage 3 and March its Stage 1 then Down
If in April its Stage 2 and March its Stage 1 then Down
If in April its Stage 3 and March its Stage 2 then Down
On the whole its not only for April vs March, it could be April vs Feb or Feb vs Jan and so on.
Hope this helps my issue.
Thanks,
Sahi
the if statement I used is the flag, i tried the data you have shown as Inline Data,
here 45016 is the numerical value of month-end of march 2023 and 45046 for april 2023
This is the table formed, all are respective dimensions and no measures.
If you want to Show stage as Stage-1, Stage -2 etc , then either use concat in UI or create a new field where you keep the real name, and same for your Month-Year Column.
You have just showed your if statement and that seems correct the only error can be that you havent ordered your data, but 9 ifs and every if having an AND condition will make the load script slower.
Hi Gabbar,
Small change for the above logic.
I made changes and created the Flag for "Up" and "Down". Both the flag values related logic is correct if we consider the dimension as Stage.
Qlik Logic(the flag what we created)
Dimension |
Up |
Down |
Stage |
Current Month Stage Value |
Current Month Stage Value |
Requirement
Dimension |
Up |
Down |
Stage |
Current Month Stage Value |
Previous Month Stage Value |
Can you please help how can I achieve the Down logic as per the ask.
Thanks
Can you explain it a bit more with example?