Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Everyone.
I am Stuck at one point in Scripting..
Want to merge two field but issue is this max of one field on the basis of group with another field.
WHat I did:
In OLD QVD: I have group and old_grp and IN NEW QVD I have group and new_grp
I concatenate both the QVD but I didn't the Output as I want.
Example
OLD QVD
Group | old_grp |
---|---|
123 | 201401 |
123 | 201402 |
123 | 201403 |
123 | 201404 |
123 | 201405 |
124 | 201501 |
124 | 201502 |
124 | 201503 |
NEW QVD
Group | old_grp |
---|---|
123 | 201401 |
123 | 201402 |
123 | 201403 |
124 | 201501 |
124 | 201502 |
OUTPUT as I want
Group | old_grp |
---|---|
123 | 201401 |
123 | 201402 |
123 | 201403 |
123 | 201404 |
123 | 201405 |
123 | 201406 |
123 | 201407 |
123 | 201408 |
124 | 201501 |
124 | 201502 |
124 | 201503 |
124 | 201504 |
124 | 201505 |
Please help me out..
Regards
Swati
Like this?
Table:
LOAD Group,
old_grp,
'Old' as Flag
FROM
[https://community.qlik.com/thread/228151]
(html, codepage is 1252, embedded labels, table is @1);
Concatenate
LOAD Group,
old_grp,
'New' as Flag
FROM
[https://community.qlik.com/thread/228151]
(html, codepage is 1252, embedded labels, table is @2);
FinalTable:
NoConcatenate
LOAD Group,
If(Group = Previous(Group) and Flag = 'New', RangeSum(Peek('old_grp'), 1), old_grp) as old_grp,
Flag
Resident Table
Order By Group, Flag desc, old_grp;
DROP Table Table;
a small change
FinalTable:
NoConcatenate
LOAD Group,
If(Group = Previous(Group) and Flag = 'New',
//RangeSum(Peek('old_grp'), 1), old_grp) as old_grp,
Date(AddMonths(Date#(Peek('old_grp') & '01', 'YYYYMMDD'), 1), 'YYYYMM'),
old_grp) as old_grp,
Flag
Resident Table
Order By Group, Flag desc, old_grp;
here in the image you can see its showing the same as earlier ..that script doesn't work correctly..
Showing 2016-5 flag1-> new output_grp ->12301 ..instead of 12314
Would you be able to share this application where you tried this out?