Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
davidstrong
New Contributor

Need some help with Load Script

I am fairly new to Load Script for QlikView and need some help with one item.

We are loading data as seen in my attachment. It has several rows of data with three columns, Business Unit, # of Invoices and TAT Result.

There is a part of the load script that I need to update to pull in just the first row of the data (row 2 of the CSV file).

This is the scrip that i have now and it is pulling in all the rows and adding them together for the TAT Result column. I am trying to use something similar to a Where clause or a SUMIF.

,Sum(IF( len(trim([TAT_Result]))>0 and  ApplyMap('mapSourceFiles', SourceFileID)='SLA 6.1', [TAT_Result]))as SLA6.1.Result
,Sum(IF(ApplyMap('mapSourceFiles', SourceFileID)='SLA 6.1', RecordCounter.Facts))
/Sum(IF(ApplyMap('mapSourceFiles', SourceFileID)='SLA 6.1', RecordCounter.Facts))as SLA6.1.Flag

! !

Any suggestions would be greatly appreciated.

Tags (1)
6 Replies
Employee
Employee

Re: Need some help with Load Script

David, I only see part of the script and it looks a bit mangled.

But in general, to read a single record, you add

WHERE(RowNo() < 1 )

at the end of the Load clause.

sravanthialuval
Contributor

Re: Need some help with Load Script

Hi,

Try this,

By using First n we can load n no. of rows from data.

Example:

First 1 LOAD * INLINE [

Category,Value

Education,800

Entertainment,500

Leadership,900

];

It will give only first row from the table.

10.PNG

rahulpawarb
Valued Contributor II

Re: Need some help with Load Script

Hello David,

If you want to pull in just the first row of the data (row 2 of the CSV file) then you can try adding below code snippet to filter the records:

WHERE RecNo()= 1;

P.S.: Fields SLA6.1.Result and SLA6.1.Flag are ambiguous to me. If possible could you please elaborate more on this.

Regards!

Rahul

davidstrong
New Contributor

Re: Need some help with Load Script

2017-02-04_19-26-22.png

This is what is in my load script. The SLA6.1.Result and SLA6.1.Flag are pulled into the dashboard to display the results. I am pretty new to load scripting and am taking over this project from someone else.

Rahul, how do I add in your Where clause to this script. I am sorry, I am still learning this.

rahulpawarb
Valued Contributor II

Re: Need some help with Load Script

Hello David,

If you have a table load as below:

Data:

LOAD Column1,

    Column2,

    Column3,

    Column4

FROM

[Data\SampleData.xlsx]

(ooxml, embedded labels, table is Sheet1);

If you would like to filter it by adding filter based on RecNo() function then refer below modified code:

Data:

LOAD Column1,

    Column2,

    Column3,

    Column4

FROM

[Data\SampleData.xlsx]

(ooxml, embedded labels, table is Sheet1)

WHERE RecNo()= 1;

Hope this will help.

Regards!

Rahul

davidstrong
New Contributor

Re: Need some help with Load Script

These are all good ideas, but still not ones that I think can help me here.

Perhaps some more context will help. This Load Statement is loading several other elements as well  and what I am needing is just the first row from one of the many items. So to add WHERE RecNo()= 1;  at the end of my Load Script would result in just the first record from each of the elements being loaded.

I need something like a Vlookup to the match to the specific row of the data.

I have made this work on the visualization side of things using this code:

IF([BUSINESS UNIT]='MyBuNAME',NUM(AVG({$<[Source File]={'SLA 6.1'},[BUSINESS UNIT]={'MyBuNAME'}>}[TAT_Result]), '0.00'), NUM(AVG({$<[Source File]={'SLA 6.1'},[BUSINESS UNIT]-={'MyBuNAME'}>}[TAT_Result]), '0.00'))

MyBuNAME replaces the actual name in the script.

If there is anyone who can offer up some help on this, I would be very grateful.

Community Browser