Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Rehan
Contributor

Loops in the Script to Add MOnths to a Date Field

I have a Date field in the script  and a Month field. Month field values are "M000", "M001", "M002" and up to "M060"

I would like to name those fields as Date + 30 day incremental.

For example "M000" should be Monthname(Date +0 )

"M001" should be Monthname(Date +30 )

"M002" should be Monthname(Date +60 ) and so o so forth

I can do that with a If statement like 

If(Month='M000',Monthname(Date+0),
If(Month='M001',Monthname(Date+30),
 If(Month='M002',Monthname(Date+60)))) etc .

But I am looking for a more robust solution using a For loop or something so I don't have to do 60 if statements.

Can anyone help. Please see the attached sample file

 

1 Solution

Accepted Solutions
Partner
Partner

Re: Loops in the Script to Add MOnths to a Date Field

Is this the expected result?

MC.PNG

Script:

Directory;

Raw:
LOAD
Date#(Date,'DD/MM/YYYY')AS Date,PN,Desc,Part,Month,Data,Date&Month as Key inline [
Date,PN,Desc,Part,Month,Data
19/11/2019,A76899,ABC,2,M000,87
19/11/2019,A76899,ABC,2,M001,8
19/11/2019,A76899,ABC,2,M002,28
];

NoConcatenate
loopData:
load distinct
Key
resident Raw;


let vRow=NoOfRows('loopData');

for i=1 to $(vRow)
let vMonth=peek('Key',$(i)-1,'loopData');

Data:

load * , Monthname(Date +(($(i)-1)*30) ) as MonthNew

resident Raw
where Key='$(vMonth)';

trace $(i) &':'&$(vMonth);
next i

drop table Raw ;

drop table loopData;

View solution in original post

6 Replies
Partner
Partner

Re: Loops in the Script to Add MOnths to a Date Field

Try this:

for i=0 to 60

Data:

load * ,  Monthname(Date +($(i)*30) ) as <YourNewColumn>

resident <YourTableName> ;

next i

drop table  <YourTableName> ;

Rehan
Contributor

Re: Loops in the Script to Add MOnths to a Date Field

YOur script is  simply generating 60 months not taking into account "M000" should  be  Nov 2019,"M001" should be Dec 2019 and so on 

Partner
Partner

Re: Loops in the Script to Add MOnths to a Date Field

I don't really get your requirement. This is your example given:

DatePNDescPartMonthData
19/11/2019A76899ABC2M00087
19/11/2019A76899ABC2M0018
19/11/2019A76899ABC2M00228

 

In Date column is showing November data, and Month column showing M000,M001 and M002.

But your requirement is M000=Nov , M001=Dec and so on.

So how is this Date column actually can be linked to M001 and M002?

 

 

Partner
Partner

Re: Loops in the Script to Add MOnths to a Date Field

Based on your requirement, sample data should looks like this:

DatePNDescPartMonthData
19/11/2019A76899ABC2M00087
19/11/2019A76899ABC2M0008
19/11/2019A76899ABC2M00028
Partner
Partner

Re: Loops in the Script to Add MOnths to a Date Field

Is this the expected result?

MC.PNG

Script:

Directory;

Raw:
LOAD
Date#(Date,'DD/MM/YYYY')AS Date,PN,Desc,Part,Month,Data,Date&Month as Key inline [
Date,PN,Desc,Part,Month,Data
19/11/2019,A76899,ABC,2,M000,87
19/11/2019,A76899,ABC,2,M001,8
19/11/2019,A76899,ABC,2,M002,28
];

NoConcatenate
loopData:
load distinct
Key
resident Raw;


let vRow=NoOfRows('loopData');

for i=1 to $(vRow)
let vMonth=peek('Key',$(i)-1,'loopData');

Data:

load * , Monthname(Date +(($(i)-1)*30) ) as MonthNew

resident Raw
where Key='$(vMonth)';

trace $(i) &':'&$(vMonth);
next i

drop table Raw ;

drop table loopData;

View solution in original post

Digital Support
Digital Support

Re: Loops in the Script to Add MOnths to a Date Field

Rehan, did Arthur's latest post help you get what you needed?  If so, please be sure to return to the thread and use the Accept as Solution button to give him credit and to let other Community Members know it helped.  If you are still working upon things, please provide an update and clarification per Arthur's comments such that we can see if we can help get things sorted.  If you figured things out, please consider posting what you did and then marking that post as the solution in that case.

Regards,
Brett

To help users find verified answers, please don't forget to use the "Accept as Solution" button on any posts that helped you resolve your problem or question.