Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
nevilledhamsiri
Specialist
Specialist

How to combine two time period & create a new field for new period

As per the attached data, it has reported Month, Paid Month but I need to create a new field as Tran_Month only when Reported Year & Month equal to Paid Year & Month in the script. After that I need to pick Month provision+ Under over adjustment for that particular created Tran_Month. How this is done.

Thanks In advance

Neville

1 Solution

Accepted Solutions
breno_morais
Partner - Contributor III
Partner - Contributor III

When you click in IT IS, just show "Reported Year & Month equal to Paid Year & Month".

View solution in original post

12 Replies
breno_morais
Partner - Contributor III
Partner - Contributor III

Can you attach simple read?

vishsaggi
Champion III
Champion III

Cannot see sample data attached.

nevilledhamsiri
Specialist
Specialist
Author

Hi, Please find the attachments

breno_morais
Partner - Contributor III
Partner - Contributor III

Good Morning Neville!

I use IF converting in Text for interpretation

Look this:

In 'IT IS' or 'NOT IS' put what you need

IF(Text(Date (REPORTED_DATE, 'MM/YYYY'))=Text(Date (PAID_DATE, 'MM/YYYY')),'IT IS','NOT IS') as TRAN_MONTH

Edit Script [CUsersbrenoDownloadsCLAIM_MY.qvw].jpg

Filter:

print1.jpg

nevilledhamsiri
Specialist
Specialist
Author

Hi Breno, Thank you very much for your response, Could you please see my requirement & see how your solution could be worked out

I need to get the claim charge which is the addition of Monthly provision & Under or over adjustment

But these two fields need to be considered only when it is "IT IS" as you rightly.

But for the data I have once this is done, only Months provision is picked but under or over adjustment are not picked. Why it is? .

Also if I am to pick the claim charge for different Month I think I need to write a set expression. Hope for that too you will help me out.

Expect you may help me on this I think with your help I am almost near to my out put

nevilledhamsiri
Specialist
Specialist
Author

Please see the qvd I tried but wont get the output

breno_morais
Partner - Contributor III
Partner - Contributor III

Can I suppose the Month have 30 days? Or Can I show remaining value in Days?

breno_morais
Partner - Contributor III
Partner - Contributor III

Neville,

I have attached an application for you see, it has two new columns. The first is interval between two dates in days and Second is interval between two dates with the value minus 30.

print2.jpg

First expression:

IF(Text(Date (REPORTED_DATE, 'MM/YYYY'))=Text(Date (PAID_DATE, 'MM/YYYY')),'NOT OVER'

,fabs(Interval(REPORTED_DATE-PAID_DATE,'d')))


Second Expression:

IF(Text(Date (REPORTED_DATE, 'MM/YYYY'))=Text(Date (PAID_DATE, 'MM/YYYY')),'NOT OVER'

,IF(fabs(Interval(REPORTED_DATE-PAID_DATE,'d'))- 30 > 0,fabs(Interval(REPORTED_DATE-PAID_DATE,'d'))-30, 0))

Is this the correct answer? If it's mark as CORRECT, it would help me a lot.

nevilledhamsiri
Specialist
Specialist
Author

ok. Say if the charge is 15000 as of  May as an example, cannot it be taken as the charge of May?