Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Creating a variable/Column(Using SQL) in Qlik sense data load editor

Hi,

I am new to qlik and am not yet completely familiar with the basics of scripting language in Qlik.

I am trying to achieve a simple indicator which tells me if a account is new or not.

below is a sample of how the data is structured(I have not shown the other variables)

acct.PNG

In the above scenario 2 and 4 are new accts as they opened in 2015.

I am thinking of simply using min(date) at a account level = '01/01/2015' to create a column that flags a acct as new/old.

(Data is loaded from excel for now but might be loaded from a SQL ODBC connector in the future)

I used the below code in the MAIN section just to check what happens. It threw a error as expected.

SQL :

select [ACCT],MIN( CASE when [Snapshot Date]='01/01/2015' THEN 1 else 0 end as [INDICATOR])

from [Sheet 1]

GROUP BY [ACCT];

How/where should I place this SQL query? Is it right at all in the first place.

Is there a way to achieve this simply in the App itself?

Is someone can provide a detailed syntax that will be great as I am starting completely fresh on this tool.

Thanks in advance!

1 Solution

Accepted Solutions

Re: Creating a variable/Column(Using SQL) in Qlik sense data load editor

If you're loading from an excel file the use LOAD instead of SELECT:

LOAD

     [ACCT],

     MIN( IF( [Snapshot Date]='01/01/2015' , 1 ,0)) as [INDICATOR]

FROM

     'lib://MyFiles/MyExcelFile.xlsx' (ooxml, embedded labels, table is [Sheet 1]);

GROUP BY

     [ACCT];

Depending on what your excel file contains you may need to use another comparison in the if statement. Perhaps this: If([Snapshot Date] = MakeDate(2015), 1, 0)


talk is cheap, supply exceeds demand
9 Replies

Re: Creating a variable/Column(Using SQL) in Qlik sense data load editor

First you need to define a connection to the database in the library. Then in the script you need to establish a connection to the database using the connection you defined. After that you can load data from the database with an sql statement:


// step 1 - make a connection to the database

LIB CONNECT TO 'MyDataSource';

// step 2 - load the data from the database with an sql select statement

MyTable:

SQL SELECT

     [ACCT],

     MIN( CASE when [Snapshot Date]='01/01/2015' THEN 1 else 0 end as [INDICATOR])

FROM

     [Sheet 1]

GROUP BY

     [ACCT];


talk is cheap, supply exceeds demand
Not applicable

Re: Creating a variable/Column(Using SQL) in Qlik sense data load editor

Hi,

I think I did not define my problem well.

I am using an excel file for the data load currently.

And I have the data loaded in the app.

Now I face a situation wherein,

  • I would like to create a calculated field. It need not be a SQL calculated field - its just something I knew. It could as well be a set expression if its possible.
  • The acct and the calculated field( new acct indicator as explained in original question) can be a separate table in my data model. I do not want to disturb the original load script. I would ideally like to include this as a separate script.

Any suggestions?

Re: Creating a variable/Column(Using SQL) in Qlik sense data load editor

If you're loading from an excel file the use LOAD instead of SELECT:

LOAD

     [ACCT],

     MIN( IF( [Snapshot Date]='01/01/2015' , 1 ,0)) as [INDICATOR]

FROM

     'lib://MyFiles/MyExcelFile.xlsx' (ooxml, embedded labels, table is [Sheet 1]);

GROUP BY

     [ACCT];

Depending on what your excel file contains you may need to use another comparison in the if statement. Perhaps this: If([Snapshot Date] = MakeDate(2015), 1, 0)


talk is cheap, supply exceeds demand
Not applicable

Re: Creating a variable/Column(Using SQL) in Qlik sense data load editor

I did not view your step 2 before replying - my bad.

Let me try out your solution and get back.

Thanks!

Not applicable

Re: Creating a variable/Column(Using SQL) in Qlik sense data load editor

Hi,

I tried putting your query in both AUTO section and the main section. I got the following error:

The [Sheet1] label comes from the AUTO generated section of the main load script.

I even tried changing the FROM tablename to that of the original excel sheet and it gives me the same error.

Capture3.PNG

Re: Creating a variable/Column(Using SQL) in Qlik sense data load editor

Ah, yes. You need to reference the source file correctly too. See the online help: http://help.qlik.com/en-US/sense/2.2/Subsystems/Hub/Content/Scripting/ScriptRegularStatements/Load.h....

Perhaps it's easier if you use the Data Manager to add your excel data: Managing data in the app with the Data manager ‒ Qlik Sense. Then you can switch to the Date load editor and unlock the automatically generated section and modify the load statement to your needs.


talk is cheap, supply exceeds demand
Not applicable

Re: Creating a variable/Column(Using SQL) in Qlik sense data load editor

Hi Gysbert,

I got a invalid expression error after referencing the path correctly. Is that because of the min expression?

Capture.PNG

Re: Creating a variable/Column(Using SQL) in Qlik sense data load editor

I don't see a GROUP BY clause in that error message. Does the load statement contain the GROUP BY that's necessary because of the use of the MIN(...) function?


talk is cheap, supply exceeds demand
Not applicable

Re: Creating a variable/Column(Using SQL) in Qlik sense data load editor

Hi,

The group by clause is below..not captured in screenshot. It however worked when I tried again

Thanks!!

Community Browser