Load the Table if the condition is satisfied else exit Script & Generate Log

[#1]

Hi All,

Can anybody please help me in the follwoing query

I have a excel file as attached, in sheet 1 i have 4 rows with columns as

Prod sales

A 10

B 20

C 30

D 40

Sheet 2

TotalNo_of_Rows Sales1

3 90

now i have another sheet (sheet2) where in i have the total number of rows & the amount for validation purpose

Now the actual Requirement is

Check if the Total number of rows recno() in Sheet1 is Equal to the value given in the sheet2 under field name as TotalNo_of_Rows or if the sum (sales) is = value of Sales1 in Sheet2 then Load the Sheet1 Table & store this Data in to a Qvd else Exit the script & generate a Log File.[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/4834.sample.xls:550:0]

Will appreciate if everyone can put some thoughts on this.. 🙂

Tx in Advance

Srini

[#2]

its not a OR condition but its a AND condition.. -:)

[Miguel_Angel_Baeyens · Accepted solution]

Instead of a loop load which may take a lot of time, I'd do something like the following (check syntax):

LOAD rows, amtFROM 4834.sample.xls(biff, embedded labels, table is Sheet2$); LET vRows = Peek('rows', 0); // gets the first record in field "rows"LET vAmt = Peek('amt', 0); // likewise with "amt" TotalRowsInData:LOAD RecNo() AS RowNo, prodFROM 4834.sample.xls(biff, embedded labels, table is Sheet1$); LET vTotalRows = Peek('RowNo', -1); DROP TABLE TotalRowsInData; Data:LOAD prod, sales, RangeSum(sales, Peek('SalesAcum', 0)) AS SalesAcumFROM 4834.sample.xls(biff, embedded labels, table is Sheet1$)WHERE RecNo() <= $(vRows) AND RangeSum(sales, Peek('SalesAcum', 0)) <= $(vAmt); STORE Data INTO Data.qvd; IF NoOfRows('Data') < $(vTotalRows) THEN Error:LOAD ReloadTime() AS Time, 'Error' AS MsgAUTOGENERATE 1; STORE Error INTO Error.qvd; DROP TABLE Error; EXIT SCRIPT; END IF

Hope this helps.

[#4]

Hey Miguel,

Thanks a Ton!!!! -:) for you help

There is a small change which is required in the script

As per your script "in the Last if statement"

IF NoOfRows('Data') < $(vTotalRows) then

store the error.qvd

but i need to have this as <> because the no of rows in either the first sheet of the noofrows value in the scond sheet will change on a daily basis based on the xls dump given to us.

I did tried doing this by placing a <> symbol but it dosent execute a error file.

" IF NoOfRows('Data') <> $(vTotalRows) then "

as i have to generate the Error File in either of these conditions

Can you please put some more light on this & will be of GR8 Help.. -:)

Thanks in Advance

Srini

[#5]

Hey Miguel,

I have done that..- 🙂

There was a small mistake which we were doing it while calling the Var now i am trying another Req where

if the Condition is statisfied then only load & store the Data.qvd else Exit script

If require i shall come back to you for help.. -:)

Tx again

Srini

[#6]

Hey Miguel,

It is also done.. 🙂

Srini