Previous function help

[jpjust]

Hi Guys,

Hope some one can help me here. An critical one, any help will be greatly appreciated.

I have an qliksense table below.

Appname                             AppID                                Publish date                       Analyzed date

App1                                     12345                                   2012-12-15                          2012-12-14

App[1]                                  23456                                   Never                                     2012-12-15

Now the output I want is below, the second record from the above. instead of Never, I want the date from the App1 and the App[1] name should change to App1. Publish date should be current date. Is this possible?

Appname                             AppID                                Publish date                       Analyzed date

App 1                                    23456                                  2012-12-15                            2012-12-15

Thanks

[Or]

What would be the logic for determining that App1 and App[1] are the same app, rather than two separate apps?

[jpjust]

Sorry the App names are App1 and App1[1]

So,may be by the name until App1.

App1[1] is same as App1. App1[1] is located in the work stream and App1 is located in a published stream.

Thanks

[Or]

So the logic is that apps are the same if the name is a perfect match up to the first square bracket, and otherwise you seem to be picking the max value from the combined rows?

If that's the case, something along the lines of (Note: I replaced the square bracket so I could use inline load, but in your case swap the '(' for '['):

Apps:
Load Subfield(Appname,'(',1) as Appname, Max(AppID) as AppID, MaxString(If([Publish date]<>'Never',[Publish date])) as [Publish date], MaxString([Analyzed date]) as [Analyzed date]
Group by Subfield(Appname,'(',1);
Load * INLINE [
Appname, AppID , Publish date , Analyzed date

App1, 12345 , 2012-12-15 , 2012-12-14
App1(1), 23456 , Never , 2012-12-15];

[jpjust]

Thank you so much, that was an awesome solution.

My qvf has little more than the inline / sample data (Eg., an join to another table) that I provided here, so I thought to share the qvf with you, probably you can advice on a solution.

If you look at the "My new sheet",  the expected output that I am looking for is the 3'rd record.

The script to consider is "QSDA" and "get_apps" and there is a join between the 2 tables.

Thank you so much again for your help!!

Thanks

[anat]

load *,if([Publish date]='Never',previous([Publish date]),[Publish date]) as new;
Load * INLINE [
Appname, AppID , Publish date , Analyzed date

App1, 12345 , 2012-12-15 , 2012-12-14
App1(1), 23456 , Never , 2012-12-15
App2, 12345 , 2012-12-16 , 2012-12-15
App2, 12345 , 2012-12-16 , 2012-12-15
App2 (1), 123456 , Never , 2012-12-16

];

exit script;

[Or]

Sorry, but I don't really follow what it is you're trying to do with that. The third line looks like it has the exact same app name as the second line to begin with, and if you're trying to do this in the front end, you could just use the modified app name as your dimension and max() on the other two fields regardless of which table they originate from. You may want to date() the app publish date or otherwise get rid of the 'Never' values (replace them with a null) so they don't complicate your max formula.

Of course, the suggestion to combine the app names won't work on some of your examples, e.g. 2.0 HRM Dashboard and 2.0 HRM Dashboard 2.0 (NIEUW). If you need to do that, you'll need to look at matching based on partial text, perhaps using https://qlikviewcookbook.com/list-recipes/#squelch-taas-accordion-shortcode-content-13 or something similar?

[jpjust]

Thanks much for the suggestions.

I am thinking and working on all possible ways to achieve the desired results.

To bring only the 3'rd record to show on the front end, what would be my complete expression on the front end?

I tried setanalysis, max, Aggr functions on the AppName field but did not work as expected.

Desired result:

AppName        createddatetime                    App Publish date

TEST                  2021-12-16                                  2012-12-17

Thanks again for your assistance.

[jpjust]

Here is my expression but did not work.

=Aggr(Only({<[App Publish Date] = {'$(=Date(Today(),'YYYY-MM-DD'))'}, createdDateTime ={max(createdDateTime)} >}[AppName]),[AppName])

[jpjust]

Hi Or - I guess your script logic worked with some minor changes to fit my environment and requirement

I am testing the data with few scenarios, will keep you updated.

Thanks for your help!!