0

I'm new to Qlik view and want to write a query to retrieve records with specific pattern. I have Account Table which has accountNo field and the values of the field are in the following patterns -

accountNo
XXXX-00-XX-XXXX
XXXX-01-XX-XXXX
XXXX-02-XX-XXXX
XXXX-03-XX-XXXX
XXXX-04-XX-XXXX
XXXX-05-XX-XXXX
XXXX-06-XX-XXXX and so on.

Note that X's are numbers, for example, 1124-00-52-6000, 2547-01-63-8552, 3697-02-56-6542 and so on. However, I'm trying to get accountNo from Accounts Table where the accountNo are XXXX-00-XX-XXXX, XXXX-01-XX-XXXX, XXXX-02-XX-XXXX, XXXX-03-XX-XXXX and XXXX-04-XX-XXXX using sql code or Qlikview syntax

Mike Eale
  • 101
  • 1
  • 9
  • Extract the information into a separate field instead. Qlikview can't index data found in the middle of another field so it has to scan the entire table for matches. Using "smart' keys like this may be useful for humans but a *very* bad idea for data processing. – Panagiotis Kanavos Jul 28 '21 at 11:13
  • @PanagiotisKanavos - Is there a way to write sql like code to do this? – Mike Eale Jul 28 '21 at 13:09
  • I don't really get your condition. If all the `X` are numbers, what makes `01` - `06` (and what are valid values for the second part) so special, that you need to handle it separately. Are there any account numbers, which have a different structure (for instance shorter, have alpha in them, ...) Please add some sampledata with values which should be included and shouldn't be included in the result and explain why they should/should not be included – derpirscher Jul 28 '21 at 13:14

2 Answers2

1

If you really just want the check the second part of the account number, and all account numbers follow the given structure exactly, a simple (yet probably quite slow) approach could be the following

select * from accounts where substring(accountno, 6, 2) in ('01', '02', '03', '04', '05' ,...)

Ie, get the part of the accountnumber which is relevant for the result and check if it matches your condition.

But preferably you would transform your datatable to support this type of query without any string manipulations, because they are quite expensive. Ie add an additional column accountMarker (or whatever you want to call it) and don't forget to adapt your inserts accordingly.

alter table accounts add accountMarker nvarchar(2)
update table accounts set accountMarker = substring(accountno, 6, 2)

You could also add a computed column, but I don't know how well this works with Qlikview

alter table accounts add accountMarker as substring(accountno, 6, 2)

Then you can just do

select * from accounts where accountMarker in ('01', '02', ...)
derpirscher
  • 14,418
  • 3
  • 18
  • 35
0

For completeness sake

Here is the Qlik option that would look and work very similar to the SQL with the benefit of not having to change the SQL source.

Assuming the 2 numbers you are looking for are always flanked by '-' on either side

substring(accountNo,'-',2)

You can use this expression to create a new field to group the accounts as 01, 02, 03 type accounts if you want. This would give you a new dimension that you could use easily in the front end visualizations / filter / set analysis options without having to redo the substring() work at every recalculation.

substring(accountNo,'-',2) as AccountType

You could use it in your where clause to get only accounts of the type you want

load * from Accounts.qvd (qvd)
where match(substring(accountNo,'-',2),'01','02','03'.....);

or combine everything into something like

load *,
     substring(accountNo,'-',2) as AccountType
where match(substring(accountNo,'-',2),'01','02','03'.....);
select * from accounts;
The Budac
  • 1,571
  • 1
  • 8
  • 10