topic Need some help with Load Script in QlikView

Need some help with Load Script

davidstrong — Mon, 30 Jan 2017 16:35:50 GMT

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.

Re: Need some help with Load Script

Ralf-Narfeldt — Tue, 31 Jan 2017 06:36:27 GMT

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.

Re: Need some help with Load Script

sravanthialuval — Tue, 31 Jan 2017 06:55:58 GMT

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.

Re: Need some help with Load Script

rahulpawarb — Tue, 31 Jan 2017 07:54:59 GMT

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

Re: Need some help with Load Script

davidstrong — Sun, 05 Feb 2017 02:46:58 GMT

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.

Re: Need some help with Load Script

rahulpawarb — Mon, 06 Feb 2017 05:11:08 GMT

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

Re: Need some help with Load Script

davidstrong — Mon, 16 Oct 2017 19:53:38 GMT

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.