Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Community,
I Have below table which is Category, Date and Sales.
Category | Date | Sales |
A | 3/16/2020 | 200 |
B | 3/16/2019 | 240 |
C | 1/12/2019 | 120 |
D | 12/12/2018 | 9 |
E | 2/3/2020 | 890 |
F | 3/12/2020 | 345 |
G | 1/1/2020 | 500 |
my scenario is - IF category is at least one A,B and E for current year then 100 else 200
Output would be the
Category | Date | Sales | OutPut Column |
A | 3/16/2020 | 200 | 100 |
B | 3/16/2019 | 240 | 200 |
C | 1/12/2019 | 120 | 200 |
D | 12/12/2018 | 9 | 200 |
E | 2/3/2020 | 890 | 100 |
F | 3/12/2020 | 345 | 200 |
G | 1/1/2020 | 500 | 200 |
Thanks in Advance
Try like:
Load
If(match(Category, 'A', 'B', 'E') and Year(Date)=Year(Today()), 100, 200) as NewCol,
*;
LOAD Category,
Date#(Date,'M/D/YYYY') as Date,
Sales
FROM
[https://community.qlik.com/t5/New-to-QlikView/Conditional-Value/td-p/1684818]
(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @1);
Try like:
Load
If(match(Category, 'A', 'B', 'E') and Year(Date)=Year(Today()), 100, 200) as NewCol,
*;
LOAD Category,
Date#(Date,'M/D/YYYY') as Date,
Sales
FROM
[https://community.qlik.com/t5/New-to-QlikView/Conditional-Value/td-p/1684818]
(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @1);
Hi @paulwalker ,
You can try this in the script:
DATA_TEMP:
LOAD * INLINE [
Category, Date, Sales
A, 3/16/2020, 200
B, 3/16/2019, 240
C, 1/12/2019, 120
D, 12/12/2018, 9
E, 2/3/2020, 890
F, 3/12/2020, 345
G, 1/1/2020, 500
];
DATA:
LOAD *,
IF(MATCH(Category,'A','B','E') AND YEAR(Date)=YEAR(TODAY()),100,200) AS OutPut
Resident DATA_TEMP;
DROP TABLE DATA_TEMP;
@JosephMorales wrote:Hi @paulwalker ,
You can try this in the script: upsers
DATA_TEMP:
LOAD * INLINE [
Category, Date, Sales
A, 3/16/2020, 200
B, 3/16/2019, 240
C, 1/12/2019, 120
D, 12/12/2018, 9
E, 2/3/2020, 890
F, 3/12/2020, 345
G, 1/1/2020, 500
];
DATA:
LOAD *,
IF(MATCH(Category,'A','B','E') AND YEAR(Date)=YEAR(TODAY()),100,200) AS OutPut
Resident DATA_TEMP;
DROP TABLE DATA_TEMP;
I have try this.
Paul, you have multiple posts, please be sure to properly close out your thread by using the Accept as Solution button on the one(s) that helped, as this provides credit for the poster and lets other members know which post(s) actually helped. It is greatly appreciate if you return and close this one out, or leave an update with what you still need.
Regards,
Brett