topic Re: How to use URL in QlikView

How to use URL

Tue, 03 Jun 2014 18:41:33 GMT

Hi Folks,

I have a data where in the field i have website URLs

Web site topic
q-on.bi

google.com

nasad-it.com

yahoo.com

yahoo.us

and etc

I want to use this url in the application and then from QV open the website how to do this.

Thanks

Re: How to use URL

its_anandrjs — Tue, 03 Jun 2014 18:48:55 GMT

Take a straight table and on that

Write a expression

='Open Website' & '<Url>' & [Web site topic]

And you have any dimension with this let me know.

Re: How to use URL

Tue, 03 Jun 2014 20:50:20 GMT

But in my database some address dont have www in the address and some are start with http://. How can i make it properly same as hyper text transfer protocl (http) and also i want to give its website name in the table. Currentely it is only OpenWebsite but i need website name there.

Re: How to use URL

MarcoWedel — Tue, 03 Jun 2014 22:02:28 GMT

Hi,

and don't forget to set the property 'representation' to 'Link':

LOAD * INLINE [
    Website
    q-on.bi
    google.com
    nasad-it.com
    yahoo.com
    yahoo.us
];

hope this helps

regards

Marco

Re: Re: How to use URL

MarcoWedel — Tue, 03 Jun 2014 22:16:02 GMT

Hi again,

one solution could be:

LOAD Distinct
  If(Left(Website, 4)='www.', 'http://'&Website, If(Left(Website, 11)='http://www.', Website, 'http://www.'&Website)) as Website
INLINE [
    Website
    q-on.bi
    google.com
    www.google.com
    http://www.google.com
    nasad-it.com
    yahoo.com
    yahoo.us
];

hope this helps

regards

Marco

Re: How to use URL

its_anandrjs — Wed, 11 Jun 2014 18:03:12 GMT

You can try this way also

=if(left(SubField([Web site topic],'//',2),3) = 'www',SubField([Web site topic],'//',2),'www.'&SubField([Web site topic],'//',2))

Re: How to use URL

Wed, 02 Jul 2014 03:43:34 GMT

How do you place a link on a dimension? I can't find the display options here...

Re: How to use URL

rwunderlich — Wed, 02 Jul 2014 04:06:51 GMT

The Link representation is only available for expressions, not dimensions.

Re: How to use URL

Wed, 02 Jul 2014 04:13:00 GMT

Thanks. That is bad news...

Re: How to use URL

rwunderlich — Wed, 02 Jul 2014 04:35:40 GMT

Can you explain why that's bad news to you? Perhaps I can suggest a solution.

-Rob

Re: How to use URL

Wed, 02 Jul 2014 06:36:34 GMT

Hi Rob

Thanks for your post, it was exactly what I am looking for. Yet I struggle to get it working as I would like.

I have a table with showing various Dimensions and one column with an expression (value).

Transactio-ID Value

1234 500

1235 750

1236 39

...

Now for each Transaction ID I have an attachment stored on a shared drive with a supporting PDF document.

Here are the problems I face:

When I add the &'<URL>'&File1 expression to the Transaction-ID Dimension, then it does show the following:

1234<URL>\\Server\Directory\File.pdf as I can't find the Display Option to represent this Dimension as a link.

I then tried to place the link on the expression (value), as I have the Display Option available here. I have added the same part to the expression. Now I get the underlined link, but the value drops to 0 instead of the 500/750/39/etc. The link itself works just fine opening the PDF I want.

If I place a new expression column into the table, using 'Attachment<URL>'&File1 I also get the link, but the value column drops completely to 0.

The value expression is as follows: -Sum({$<Debug={'Actuals'}>} If(AC3='510', BalanceLC))

I don't think the Sum should be a problem, as there is a 1-1 relationship, as we are on the transaction level already.

Any idea how to get it shown in the dimension as link? Or to get the values re-appear?

Thanks

Re: How to use URL

rwunderlich — Wed, 02 Jul 2014 13:15:38 GMT

1. Add Tranid as dimension

2. Add an expression that contains the Link: =Tranid &'<URL>'&File1.

3 Add the Sum(Value) expression.

On the Presentation tab, Hide the Dimension column.

-Rob

Re: How to use URL

Wed, 02 Jul 2014 14:06:58 GMT

Thanks Rob

That looks kinda very good, the only thing is, I have now many more transactions shown which don't match the criteria set in the SUM(Value) expression.

I'm using a set expression here Sum({$<Debug={'Actuals'}>} If(AC3='510', BalanceLC)) which only shows "Actuals" (no Budget values) and only shows Transaction from Accounts = 510.

The table now shows transactions which are not related to Account 510 (as value 0) but are still not relevant for my report. How can I use the same set expression for the TranID?

Re: How to use URL

rwunderlich — Thu, 03 Jul 2014 00:33:53 GMT

Use only( tranid) in the link expression and include the set expression. You should add AC3={510} in the set.

Rob

Re: How to use URL

Thu, 03 Jul 2014 01:42:09 GMT

Hmm, I'm not quite clear I understand.

1. In TranID expression I changed nothing, and in the value expression I added the AC3 to show the following: -Sum({$<Debug={'Actuals'}>+<AC3='510'>} If(AC3='510', BalanceLC))
It still shows rows from all other accounts

2. Whenever I try to add a set to the TranID expression (TransId&'<URL>'&File1), I get an error in the expression

Can I filter for these accounts somewhere in the dimension?

Re: How to use URL

rwunderlich — Thu, 03 Jul 2014 03:17:02 GMT

Yes, you could filter the accounts in the dimension like:

aggr({$<Debug={'Actuals'},AC3={'510}'>}Only(TranID), TranId)

In the balance expression the set would be the same.

-Sum({$<Debug={'Actuals'},AC3={'510}'>} BalanceLC))

Note that your error is in using the Union + with two sets. You actually want one set with a comma between the two modifiers. That's an "AND".

Your URL expression would be:

Only({$<Debug={'Actuals'},AC3={'510}'>} TranID) &'<URL>'&File1

If you filter in the dimension, you should be able to get by with:

TranID) &'<URL>'&File1

assuming there is only one File1 associated with a TranID. If filtering in the dimension, the set would also be unnecessary in the balance expression assuming a TranID contained only one Debug value and one AC3 value. If n doubt, include the set in the expressions as well.

-Rob

Re: How to use URL

Thu, 03 Jul 2014 08:36:27 GMT

Dear Rob

I really appreciate your help, yet also this did not do the trick. I keep seeing all those other transactions which are irrelevant. I will go for the simple solution, and just add a list box and filter through that.

Maybe I will try at a later stage to get this going. But at least I have now the link on the transaction, which is already awesome The additional click on the list box is not really that difficult.

Thanks again.

Re: How to use URL

rwunderlich — Thu, 03 Jul 2014 13:07:07 GMT

If you can upload a sample qvw, I'll take a look.

-Rob

Re: How to use URL

Mon, 07 Jul 2014 03:45:26 GMT

Hi Rob

As it's with real data, I can't really upload this.

Re: How to use URL

Mon, 07 Jul 2014 03:56:46 GMT

But I just found out the trick:

When I put =If(Debug='Actuals' AND AC3='510',TransId,Null()) into the Dimension, then it filters all the empty values out and only shows the ones I want