topic Re: Previous function help in App Development

Previous function help

jpjust — Wed, 15 Dec 2021 19:35:32 GMT

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

Re: Previous function help

Or — Wed, 15 Dec 2021 20:37:38 GMT

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

Re: Previous function help

jpjust — Thu, 16 Dec 2021 13:43:14 GMT

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

Re: Previous function help

Or — Thu, 16 Dec 2021 14:03:37 GMT

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];

Re: Previous function help

jpjust — Thu, 16 Dec 2021 17:22:43 GMT

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

Re: Previous function help

anat — Thu, 16 Dec 2021 18:35:30 GMT

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;

Re: Previous function help

Or — Thu, 16 Dec 2021 18:59:05 GMT

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?

Re: Previous function help

jpjust — Fri, 17 Dec 2021 15:08:15 GMT

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.

Re: Previous function help

jpjust — Fri, 17 Dec 2021 15:53:33 GMT

Here is my expression but did not work.

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

Re: Previous function help

jpjust — Fri, 17 Dec 2021 21:58:19 GMT

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!!

Re: Previous function help

jpjust — Wed, 05 Jan 2022 21:52:09 GMT

Hi Or - I am still struggling with this and I tried various ways but unable to achieve the desired results.

Here is my script:

[Script1]

LOAD
[appId] as AppID,
[appName],
timestamp([createdDateTime]) as createdDateTime,
[flagCount]
RESIDENT RestConnectorMasterTable;

[Script2]

LOAD

[id_u3] AS AppID,
[name_u2] AS AppName,
if(left(publishTime,4)='1753','Never',date(timestamp(publishTime))) AS [App Publish Date]
RESIDENT RestConnectorMasterTable

Here is the output:

The output that I want is below:

Any help to achieve the above output will be appreciated.

Thanks