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

[#1]

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)

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!

[Gysbert_Wassenaar]

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];

[#3]

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?

[Gysbert_Wassenaar · Accepted solution]

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)

[#5]

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

Let me try out your solution and get back.

Thanks!

[#6]

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.

[Gysbert_Wassenaar]

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.

[#8]

Hi Gysbert,

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

[Gysbert_Wassenaar]

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?

[#10]

Hi,

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

Thanks!!