topic Re: Convert SQL to QlikView Script in QlikView

Convert SQL to QlikView Script

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

Please help me to convert this SQL query to QlikView script.

select count(1)

from [AAM].[dbo].metadata1 as a,

[AAM].[dbo].metadata2 as b

where

a.asset_folder=b.asset_folder

and (a.o_type='doc1' or (a.o_type='doc2' and a.path not like '%/division/%'))

and (a.inactive_date='' or a.inactive_date is null or try_parse(a.inactive_date as date)> getutcdate())

Siva_Sankar — Mon, 02 Apr 2018 01:32:53 GMT

If the sql returns data..why cant you try preceding load..

Load *;

sql select count(1)

from [AAM].[dbo].metadata1 as a,

[AAM].[dbo].metadata2 as b

where

a.asset_folder=b.asset_folder

and (a.o_type='doc1' or (a.o_type='doc2' and a.path not like '%/division/%'))

and (a.inactive_date='' or a.inactive_date is null or try_parse(a.inactive_date as date)> getutcdate());

-Siva

sasiparupudi1 — Tue, 03 Apr 2018 10:46:04 GMT

Something like below

Load *

Resident metadata1

Where

Match(o_type,'doc1','doc2') and WildMatch(path,'*division*')=0

And (Len(Trim(inactive_date))=0 Or inactive_date>UTC())

;

inner join(a)

Load

asset_folder

Resident metadata2

;

CountRecords:

Load Count(asset_folder) as Count

Resident a

;

if you want the count in a variable, try as below

Let vCount=Peek('Count',0,'CountRecords');

hth