manipulate date

[cedfoning]

Hello, i have an issue

i have a table Campaign with the following

Campaign id, campaign.year

1, 2020

2, 2019

3, 2018, 

4, 2017

i want to create another table, in script automatically, related to this. like this 

campaign.year, campaign.ref

2021,2020
2021,2019
2021,2018
2021,2017
2020,2019
2020,2018
2020,2017
2019,2018
2019,2017

I've done a load inline table manually in the script, but i really want to have the table automatically. 

At the end, when i select the year 2020 on filter campaign.year  for exemple, i want to have in the filter campaign.ref al the years EXCEPT FROM THE CURRENT SELECTED YEAR, AND YEARS ABOVE

Can someone help please

[rwunderlich]

How about:

Data:
LOAD * Inline [
Campaign id, campaign.year
1, 2020
2, 2019
3, 2018
4, 2017
]
;
Ref:
Load campaign.year
Resident Data
;
Outer Join (Ref)
LOAD campaign.year as campaign.ref
Resident Data
;
Inner Join(Ref)
LOAD *
Resident Ref
Where campaign.ref < campaign.year
;
 

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[cedfoning]

Hello @rwunderlich 

if the dates are generated automatically (for example for the years 2022, 2022, 2023, etc

how can it be done ? 

thanks 

[rwunderlich]

Can you clarify? You want to generate future years?  Or you want all years in a range?

-Rob

[cedfoning]

Hello @rwunderlich , 

what i mean is this : 

for 2018, i will have 2017

for 2019, i will have 2018 & 2017

for 2020, i will have 2019, 2018, 2017

for 2021, i will have 2020, 2019, 2018, 2017

for 2022 (upcoming campaign.year), i will have 2021, 2020, 2019, 2018, 2017

etc

And i want it to be generated automatically

hope i'm more clear

[rwunderlich · Accepted solution]

It would be something like this:

Data:
LOAD * Inline [
Campaign id, campaign.year
1, 2020
2, 2019
3, 2018
4, 2017
]
;
Ref:
Load campaign.year,
campaign.year - IterNo() as campaign.ref
Resident Data
While campaign.year - IterNo() <= 2017
;

(not syntax checked)

-Rob