topic Re: Need help with logic in QlikView

Need help with logic

jyothish8807 — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I am stuck with a simple requirement and would appreciate your help in this:

I would like to know the user who made his first transaction in year 2012 and what what is the total sale done by him in a straight chart.

A:
LOAD * INLINE [
Year, Amt, User
2010, 100, A
2011, 200, B
2012, 500, C
2013, 400, D
2013, 900, B
2015, 100, C
];

Output: C =600

Re: Need help with logic

tresB — Mon, 31 Dec 2018 06:02:48 GMT

Hi Jyothish, May be like this?

Sum({<User={"=min(Year)=2012"}>}Amt)

Edit: simplified the expression

Re: Need help with logic

jyothish8807 — Mon, 31 Dec 2018 06:04:59 GMT

Thanks a lot for your help @tresB 🙂

Re: Need help with logic

avinashelite — Mon, 31 Dec 2018 06:06:09 GMT

Try like this

A:
LOAD *,
If(Year<>Previous(Year) and User<>Previous(User) and Year='2012' ,1,0) as Flag;
LOAD * INLINE [
Year, Amt, User
2010, 100, A
2011, 200, B
2012, 500, C
2013, 400, D
2013, 900, B
2015, 100, C
];

User_mapping:
Mapping
LOAD
User,
Flag
Resident
A;

LOAD
Year,
User,
Amt,
ApplyMap('User_mapping',User,0) as User_Flag
Resident
A;

DROP Table A;

Re: Need help with logic

jyothish8807 — Mon, 31 Dec 2018 06:19:59 GMT

Thanks for the help bro 🙂