topic Getting the integers from the db in QlikView

Getting the integers from the db

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I have my qvw file connected to a db through ODBC. I'm fetching the data using LOAD statement, where I'm loading each column as a unique file name. the data that is saved in the table is in the form of VARCHAR. The data is differentiated on the basics of week number, meaning week1 has a value in requirements as' 7/11 requirements have been meet', but for week 2 has value 'Requirement 10 has been accomplished' .And in the end the data is to be shown in the text box object. I'm using" =Concat(requirement,chr(13)) " in the text box properties [requirement being the column name]

Problem: How to extract just the integer values that are available in the column? How to show the distinct values in the textbox?

what should I be doing here?

Kindly help me out. Let me know if anything is not understandable.

Re: Getting the integers from the db

sarafamiglietti — Wed, 07 Feb 2018 10:15:20 GMT

Hello,

Can you give an example of your script?

Thanks and Regards

Re: Getting the integers from the db

tresB — Wed, 07 Feb 2018 10:24:20 GMT

To extract the numbers, you can probably use KeepChar(), like:

KeepChar('Requirement 10 has been accomplished', '1234567890') -> returns 10

Re: Getting the integers from the db

Anonymous — Wed, 07 Feb 2018 10:32:31 GMT

This is what I'm trying out : please recommend :

load

mid(Requirement,0,4) as Requirements,

/*With this I'm getting the initial 4 values of all the rows in the column*/

/*SubField(Requirement,' ',1) as requirement,*/

/*Didn't get any answer*/

SQL SELECT *

FROM database.`xml`; /*Database is the db name and xml is table name, requirements is the column name*/

Re: Getting the integers from the db

Anonymous — Wed, 07 Feb 2018 10:43:09 GMT

I really thought it would work.But it is not showing any data. I thin the issue is in the display of text box, I used your query KeepChar(requirements, '1234567890') as requirements, the n in text box properties, I tried = requirements.

Kindly help out

Re: Getting the integers from the db

tresB — Wed, 07 Feb 2018 10:46:12 GMT

This works textbox only when there is a single record in the scope. Select any requirement, you would see it's value in the textbox.

Re: Getting the integers from the db

sarafamiglietti — Wed, 07 Feb 2018 11:00:58 GMT

I agree with Tresesco.

Formula will work in the text box if you select a requirement.

Without selection:

With a selection:

Re: Getting the integers from the db

Anonymous — Wed, 07 Feb 2018 11:39:08 GMT

Just to clarify, the there are two columns requirements and week. So could you tell me how to select a single one. I'm not sure how to do it, because I'm directly fetching it from the the db and can't have a list box present there in the display as mentioned by @sara. I hope I'm understandable.

Re: Getting the integers from the db

sarafamiglietti — Wed, 07 Feb 2018 11:43:42 GMT

I'm not sure to understand your problem.

Can you give us your Qlikview application (or a simplified version if data are private)?

Re: Getting the integers from the db

Anonymous — Wed, 07 Feb 2018 12:00:16 GMT

Actually this is the simplified version, data is confidential that is why I'm unable to share the code with you our the qvw file.

Please see the screenshots, hopefully this will bring about some clarification :

I was hoping here in text box I would only see 7/11 and in another text box just 10. Kindly help

Re: Getting the integers from the db

sarafamiglietti — Wed, 07 Feb 2018 14:22:41 GMT

As explained, it's not possible in a text box to do this.

Maybe you can handle it with set analysis, but I am not convinced by this solution because Requirement is a dimension and not an indicator which can be used in an aggregation function like sum, max etc.

Re: Getting the integers from the db

Anonymous — Thu, 08 Feb 2018 05:17:41 GMT

Hi Sara, I don't have licensed software so I won't be able to view your file. Please if you can share the code screenshot that will be really helpful. Also I'll start looking into Set Analysis

Re: Getting the integers from the db

sarafamiglietti — Thu, 08 Feb 2018 09:25:08 GMT

Hello,

Here the screenshot of the result and the corresponding formula

max({$ <WW = {1}>} KeepChar( Requirement, '1234567890') )

max({$ <WW = {2}>} KeepChar( Requirement, '1234567890') )