Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
gordanalozo
New Contributor

Load previous week data as additional column

Hi all!

I have a problem that I was trying to solve for a few days, but still cannot get it correct when I load data.

I have current table like this:

Opportunity No

Opp. Value

Script Execution Date

Script Execution Week

123

1000

2017-7-1

2017-26

123

1200

2017-7-8

2017-27

123

1300

2017-7-15

2017-28

I would like to have a table loaded like this:

Opportunity No

Opp. Value

Previous Week Opp Value

Script Execution Date

Script Execution Week

Prev Script Execution Week

123

1000

null

2017-7-1

2017-26

2017-25

123

1200

1000

2017-7-8

2017-27

2017-26

123

1300

1200

2017-7-15

2017-28

2017-27

I have all figured out, except how to load “Previous Week Opp Value”.

For some reason I am stuck with it :/, and it really should not be that difficult… I would appreciate any suggestion.


Thank you!!

Tags (1)
1 Solution

Accepted Solutions
effinty2112
Honored Contributor

Re: Load previous week data as additional column

Hi Gordana

This script will work as long as the data is loaded in date order:

Data:

LOAD [Opportunity No],

     [Opp. Value],

     Previous([Opp. Value]) as [Previous Week Opp Value],

     Date([Script Execution Date],'YYYY-M-D') as [Script Execution Date],

     Year(Date([Script Execution Date],'YYYY-M-D')) & '-' & Week(Date([Script Execution Date],'YYYY-M-D')) as [Script Execution Week],

     Year(Date([Script Execution Date],'YYYY-M-D')-7) & '-' & Week(Date([Script Execution Date],'YYYY-M-D')-7) as [Prev Script Execution Week]

FROM

Book1.xlsx

(ooxml, embedded labels, table is Sheet1);

OR

You can keep the script really simple:

Data:

LOAD [Opportunity No],

     [Opp. Value],

     Date([Script Execution Date],'YYYY-M-D') as [Script Execution Date]

FROM

Book1.xlsx

(ooxml, embedded labels, table is Sheet1);

and use chart expressions:

Opportunity No Opp. Value

Previous Week

Opp Value

Script Execution

Date

Script Execution

Week

Prev Script Execution

Week

1231000 2017-7-12017-262017-25
123120010002017-7-82017-272017-26
123130012002017-7-152017-282017-27

Dimensions are [Opportunity No] and [Script Execution Date]

Expressions:

Opp. Value = sum([Opp. Value])

Previous Week Opp Value = Above(TOTAL sum([Opp. Value]))

Script Execution Week =

Year(Date([Script Execution Date],'YYYY-M-D')) & '-' & Week(Date([Script Execution Date],'YYYY-M-D'))

Prev Script Execution Week=

Year(Date([Script Execution Date],'YYYY-M-D')-7) & '-' & Week(Date([Script Execution Date],'YYYY-M-D')-7)

Regards

Andrew

6 Replies
gordanalozo
New Contributor

Re: Load previous week data as additional column

Just to add the Load script:

Load Opportunity_No,

          Opp_Value,

          Script_Execution_Date,

          Script_Execution_Week,

          If(week(Script_Execution_Date)=1,  Year(Script_Execution_Date)-1 & '-' & week(Script_Execution_Date - 7), Year(Script_Execution_Date) & '-' & week(Script_Execution_Date - 7)) as Previous_Script_Execution_Week;

mounachandra1
Contributor

Re: Load previous week data as additional column

Please look at the attached qvw.

Capture1.PNG

Capture.PNG

gordanalozo
New Contributor

Re: Load previous week data as additional column

Hi Mouna,

I will take a look at this and try to apply to my Load script. I have Qlik Sense so I cannot open the file you have attached as your solution. I will let you know if it works.

Thanks!

effinty2112
Honored Contributor

Re: Load previous week data as additional column

Hi Gordana

This script will work as long as the data is loaded in date order:

Data:

LOAD [Opportunity No],

     [Opp. Value],

     Previous([Opp. Value]) as [Previous Week Opp Value],

     Date([Script Execution Date],'YYYY-M-D') as [Script Execution Date],

     Year(Date([Script Execution Date],'YYYY-M-D')) & '-' & Week(Date([Script Execution Date],'YYYY-M-D')) as [Script Execution Week],

     Year(Date([Script Execution Date],'YYYY-M-D')-7) & '-' & Week(Date([Script Execution Date],'YYYY-M-D')-7) as [Prev Script Execution Week]

FROM

Book1.xlsx

(ooxml, embedded labels, table is Sheet1);

OR

You can keep the script really simple:

Data:

LOAD [Opportunity No],

     [Opp. Value],

     Date([Script Execution Date],'YYYY-M-D') as [Script Execution Date]

FROM

Book1.xlsx

(ooxml, embedded labels, table is Sheet1);

and use chart expressions:

Opportunity No Opp. Value

Previous Week

Opp Value

Script Execution

Date

Script Execution

Week

Prev Script Execution

Week

1231000 2017-7-12017-262017-25
123120010002017-7-82017-272017-26
123130012002017-7-152017-282017-27

Dimensions are [Opportunity No] and [Script Execution Date]

Expressions:

Opp. Value = sum([Opp. Value])

Previous Week Opp Value = Above(TOTAL sum([Opp. Value]))

Script Execution Week =

Year(Date([Script Execution Date],'YYYY-M-D')) & '-' & Week(Date([Script Execution Date],'YYYY-M-D'))

Prev Script Execution Week=

Year(Date([Script Execution Date],'YYYY-M-D')-7) & '-' & Week(Date([Script Execution Date],'YYYY-M-D')-7)

Regards

Andrew

gordanalozo
New Contributor

Re: Load previous week data as additional column

Thank you so much! It worked great, that is exactly what I needed!

effinty2112
Honored Contributor

Re: Load previous week data as additional column

Hi Gordana,

Really pleased to have helped!

Community Browser