topic Re: SAS/SQL Script to Qlikview Script in QlikView

SAS/SQL Script to Qlikview Script

Thu, 11 Feb 2016 16:11:51 GMT

Good day,

I have the below SAS code, I want it to code it in Qlikview script,

How should I go about?

Thanks a lot in advance

/* Accounts for which billed amount >0 */

data prev_balanced;

set port_&prev_month.(keep=id_acc PREV_STATEMNT_ACCT_BAL ect_flag class);

where PREV_STATEMNT_ACCT_BAL>0;

run;

/* Dataset for transactor accounts */

proc sql;

create table transactor as

select * , 1 as transactor_flag

from active_accounts_final

where ect_flag ne 1 and id_acc in

(select id_acc from prev_balanced);

quit;

/* Dataset for revolver accounts */

proc sql;

create table revolver as

select *

from active_accounts_final

where ect_flag = 1 and id_acc in

(select id_acc from prev_balanced);

quit

Re: SAS/SQL Script to Qlikview Script

Gysbert_Wassenaar — Thu, 11 Feb 2016 16:23:10 GMT

You can use the sql select statements, but not the sas script. And I don't know sas script so I can't translate that part

This will create the transactor and revolver tables:

LEFT KEEP (prev_balanced)

transactor:

select * , 1 as transactor_flag

from active_accounts_final

where ect_flag ne 1 ;

LEFT KEEP (prev_balanced)

revolver:

select *

from active_accounts_final

where ect_flag = 1;

Note, you first need to create a connection to the source database before you can load data from it.

Re: SAS/SQL Script to Qlikview Script

Thu, 11 Feb 2016 17:32:58 GMT

Thanks Gysbert,

Does that mean I have to create that table prev_balance before executing this piece of code?

Re: SAS/SQL Script to Qlikview Script

Gysbert_Wassenaar — Thu, 11 Feb 2016 18:19:30 GMT

Yes, it does.

Re: SAS/SQL Script to Qlikview Script

Mon, 15 Feb 2016 06:20:28 GMT

so for the below code:

select sum(total_limit)

from active_accounts_final

where credit_balance>0

and account_number

not in (select account_number

from transactor)

I must use the following syntax??:

left keep (transactor)

load sum(total_limit)

from active_accounts_final

where credit_balance > 0

Re: SAS/SQL Script to Qlikview Script

Gysbert_Wassenaar — Mon, 15 Feb 2016 17:36:32 GMT

I'm pretty sure that that will not work. In Qlikview Load sum(something) from .... will return only one number if you're not using a group by clause. And the field name will be literally 'sum(something)' if you don't supply a name: sum(something) as Sum_of_Something. And since there's no field name to associate it with other tables your result table will not be associated with anything else in your data model.

Perhaps you should start with explaining what your data sources are and what kind of data model you're trying to create from that.

Re: SAS/SQL Script to Qlikview Script

Tue, 16 Feb 2016 07:23:28 GMT

Basically here I'm trying to mimic a SAS code to perform same functionality and to produce the same output (values).

I'm drawing data from a huge file (Portfolio), then I create sub data-sets.

To reference the sub data-sets i have created, then after I perform front end metric calculations.

Those sun-data are prev_balanced, transactor and revolver

Please note: I have already created the sub data-set called active_accounts_final

/* Accounts for which billed amount >0 */

data prev_balanced;

set portfolio(keep=id_acc PREV_STATEMNT_ACCT_BAL ect_flag class);

where PREV_STATEMNT_ACCT_BAL>0;

run;

/* Dataset for transactor accounts */

proc sql;

create table transactor as

select * , 1 as transactor_flag

from active_accounts_final

where ect_flag ne 1 and id_acc in

(select id_acc from prev_balanced);

quit;

/* Dataset for revolver accounts */

proc sql;

create table revolver as

select *

from active_accounts_final

where ect_flag = 1 and id_acc in

(select id_acc from prev_balanced);

quit

;

Re: SAS/SQL Script to Qlikview Script

Gysbert_Wassenaar — Tue, 16 Feb 2016 16:14:34 GMT

I have already created the sub data-set called active_accounts_final

Is it a file, a database table or a table already loaded in the qlikview document? And where does the data for prev_balanced come from?

Re: SAS/SQL Script to Qlikview Script

Fri, 19 Feb 2016 14:29:39 GMT

prev_balance data comes from the file portfolio

Re: SAS/SQL Script to Qlikview Script

Gysbert_Wassenaar — Fri, 19 Feb 2016 16:58:28 GMT

And the other data? Those come from files too? What kind of files are these files? Text files, excel sheetsl, xml files?

Re: SAS/SQL Script to Qlikview Script

Mon, 22 Feb 2016 06:17:26 GMT

Yes they come from that file too. they are in excel

Re: SAS/SQL Script to Qlikview Script

Gysbert_Wassenaar — Mon, 22 Feb 2016 15:56:46 GMT

Great. Create a small extract from that excel file that we can use as sample data.