topic Re: AddYears() function doesn't work properly with Concatenate in Reporting Service & Alerting

AddYears() function doesn't work properly with Concatenate

Adilet_Yessaliyev — Tue, 12 Apr 2022 09:00:06 GMT

Hi Everyone! I've got a problem in Script Loader in Qlik Sense Desktop. I have two .qvd files one loaded via SQL script (Let's say QVD1) another loaded from static .xlsx (QVD2) file. They are absolutely the same.

1. I've loaded firstly QVD1 then concatenated QVD2. I've even solved the problem of duplicated dates while concatenation (Thanks a lot to Qlik Community);

2. Then I've created second table based on the concatenation using Addyears() function, that supposed to be the data of previous year;

3. And at last joined it to the main table through left join.

Unqualify *; [Tab1]: LOAD [Flag], [City], Num#([Year]) as [Year], Date(MonthEnd(Date(MakeDate(Year(Date#([Created Dtae], 'YYYY-MM-DD')),Month(Date#([Created Dtae], 'YYYY-MM-DD')),Day(Date#([Created Dtae], 'YYYY-MM-DD'))), 'DD.MM.YYYY'))) AS [Created Dtae], [Month], [Amount] FROM QVD1 (qvd); Concatenate [Tab1]: LOAD [Flag] AS [Flag], [City] AS [City], Num#([Year]) AS [Year], MonthEnd([Created date]) as [Created Date], [Month] AS [Month], [Amount] FROM QVD2 (qvd); [Tab2]: Load [Flag] as [FlagLastYear], [City] as [CityLastYear], [Created Date] as [Created Date Last], [Amount] as [AmountLast] Resident [Tab1]; Left Join ([Tab1]) Load [FlagLastYear] as [Flag], [CityLastYear] as [City], [Created Date Last] as [Created Date], [AmountLast] Resident [Tab2]; Drop Table [Tab2];

It's what I'm trying to do)))

And this what I've got as a result
Can anyone explain me what's wrong with this script?
Thanks in advance

Re: AddYears() function doesn't work properly with Concatenate

Or — Tue, 12 Apr 2022 09:18:31 GMT

Probably the typo in 'date':

[Created Dtae],

Re: AddYears() function doesn't work properly with Concatenate

Adilet_Yessaliyev — Tue, 12 Apr 2022 09:30:29 GMT

Never mind typos - I've changed the real names in order with Security Requirements

Re: AddYears() function doesn't work properly with Concatenate

Or — Tue, 12 Apr 2022 09:39:04 GMT

Well, you can add to the list of things you seem to have misplaced the AddYears() function - there isn't one in your code 🙂

It may be a good idea to either post the original or clean up the changed version.

Re: AddYears() function doesn't work properly with Concatenate

Adilet_Yessaliyev — Tue, 12 Apr 2022 10:03:11 GMT

Oh My Goodness)))) Sorry

Unqualify *; [Tab1]: LOAD [Flag], [City], Num#([Year]) as [Year], Date(MonthEnd(Date(MakeDate(Year(Date#([Created Date], 'YYYY-MM-DD')),Month(Date#([Created Date], 'YYYY-MM-DD')),Day(Date#([Created Date], 'YYYY-MM-DD'))), 'DD.MM.YYYY'))) AS [Created Dtae], [Month], [Amount] FROM QVD1 (qvd); Concatenate [Tab1]: LOAD [Flag] AS [Flag], [City] AS [City], Num#([Year]) AS [Year], MonthEnd([Created date]) as [Created Date], [Month] AS [Month], [Amount] FROM QVD2 (qvd); [Tab2]: Load [Flag] as [FlagLastYear], [City] as [CityLastYear], AddYears([Created Date], 1) as [Created Date Last], [Amount] as [AmountLast] Resident [Tab1]; Left Join ([Tab1]) Load [FlagLastYear] as [Flag], [CityLastYear] as [City], [Created Date Last] as [Created Date], [AmountLast] Resident [Tab2]; Drop Table [Tab2];

Re: AddYears() function doesn't work properly with Concatenate

Or — Tue, 12 Apr 2022 10:14:25 GMT

There's still the issue of Dtae being typo'd, but as you said, let's ignore that (but if that's the case in the original code, it could actually result in the exact problem you're describing because there won't be a concatenate or join on the date field since it's not spelled the same).

One thing I'd try and check is the consistency of the date fields - in one case you are applying Date() to it and in another you're just using MonthEnd, and in the third you're just using AddYears(). I'd recommend using the same approach for all three and see if that gets them on the same page. I'm not sure if it's related, but without access to the data or a clear understanding of what's happening behind the scenes (for example, what is the "Previous Year" field in your table that's not in your script?) it's hard to guess what the issue might be.

Re: AddYears() function doesn't work properly with Concatenate

rwunderlich — Tue, 12 Apr 2022 16:13:12 GMT

Are you trying to create additional rows for "last year" or additional fields in the existing rows?

-Rob