Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Not applicable

How to updated only the last row in the record??

Dear All,

I got a table as attached, while converting the loaded excel file into QVD I want to load only >= the date exist in the excel.

please see the highlighted row in the excel.  jagan mohan

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: How to updated only the last row in the record??

Hi,

Check this script

Data:

LOAD Company,

     Department_CUTTING,

     Factory_CUTTING,

     Owner_CUTTING,

     DATE,

     [Plan 2day ready to issue stock_CUTTING],

     [Actual 2day ready to issue stock_CUTTING],

     [Plan BPCD hit rate_CUTTING],

     [Actual BPCD hit rate_CUTTING],

     [Plan SPCD hit rate_CUTTING],

     [Actual SPCD hit rate_CUTTING],

     [Plan FPY_CUTTING],

     [Actual FPY_CUTTING],

     [Plan Cutting Overall Efficiency_CUTTING],

     [Actual Cutting Overall Efficiency_CUTTING],

     [Plan Cutplan vs Acheivement_CUTTING],

     [Actual Cutplan vs Acheivement_CUTTING],

     [Plan OTD_CUTTING],

     [Actual OTD_CUTTING],

     [Plan Cut sending plan  vs Acheivement_CUTTING],

     [Actual Cut sending plan  vs Acheivement_CUTTING]

FROM

[CUTTING.qvd]

(qvd);

Temp:

LOAD

Max(DATE) AS MaxDate

RESIDENT Data;

LET vMaxDate = Date(Peek('MaxDate'));

DROP TABLE Temp;

Concatenate(Data)

LOAD Company,

     Department,

     Factory,

     Owner,

     Date(Month) as DATE,

     Accounts,

     [Plan PSD],

     [Actual PSD],

     [Plan Bulk PCD],

     [Actual Bulk PCD],

     [Plan Sizeset PCD],

     [Actual Sizeset PCD],

     [Plan Ordership],

     [Actual Ordership],

     [Plan Lost time],

     [Actual Lost time]    

FROM

[FM.xls]

(biff, embedded labels, table is FM$)

WHERE Date(Month) > '$(vMaxDate)';

Regards,

Jagan.

15 Replies
MVP & Luminary
MVP & Luminary

Re: How to updated only the last row in the record??

Hi,

Can you explain bit more about your requirement, I want to load only >= the date exist in the excel.  Can you explain with an example.

Regards,

Jagan.

sushil353
Honored Contributor II

Re: How to updated only the last row in the record??

hi,

Check this out: http://community.qlik.com/docs/DOC-4597

HTH

Sushil

MVP & Luminary
MVP & Luminary

Re: How to updated only the last row in the record??

Hi,

Try like this

Temp:

LOAD

Date(Max(DATE)) AS MaxDate

FROM ExcelFile;

LET vMaxDate = Peek(MaxDate);

DROP TABLE Temp;

Data:

LOAD

*

FROM ExcelFile

WHERE DATE = '$(vMaxDate )';

Hope this helps you.

Regards,

Jagan.

Highlighted
renjithpl
Valued Contributor

Re: How to updated only the last row in the record??

May be like this ?/

Main:
LOAD Date(DATE, 'DD/MM/YYYY') as DATE,
Buyer, Company, Qty, Value
FROM

(
ooxml, embedded labels, table is Sheet1, filters(
Remove(Row, Pos(Top, 2)),
Remove(Row, Pos(Top, 1))
));
inner join
load
(
Max(Date(DATE, 'DD/MM/YYYY'))) as DATE Resident

Main;

Not applicable

Re: How to updated only the last row in the record??

ANY ONE??????

anbu1984
Honored Contributor III

Re: How to updated only the last row in the record??

Do you want to load only the row with maximum date into QVD?

neetha_p
Honored Contributor

Re: How to updated only the last row in the record??

Hi,

Please elaborate on your question.

As per your request 'I want to load only >= the date exist in the excel.'

They are no records after the line highlighted in excel file , so which data you want to load and save to QVD.

Regards

Neetha

Not applicable

Re: How to updated only the last row in the record??

Thanks for replaying.

Dear neetha P, anbu cheliyan, jagan

well, let's say I made initial load and made the QVD.... So QVD will be having a max(date)

and am having an excel as source data where user update huge set of records every time

when I load the excel for next time I want to load only the records more than or equal to qvd max date from excel and write only that records into the qvd


rather than loading everything from excel and over writing the qvd.


I want this because since the data source is in excel I won't be know if any modifications went on excel

So I don't want past records to be change I want to change the record more than or equals to QVD max date.  is there any way of doing this?

anbu1984
Honored Contributor III

Re: How to updated only the last row in the record??

Qvd: LOAD Date(Max(DATE)) AS MaxDate FROM Qvd(Qvd); 


LET vMaxDate = Peek('MaxDate',0,'Qvd');  


Data: LOAD * FROM ExcelFile WHERE DATE >= '$(vMaxDate)';

Concatenate

Load * From Qvd(Qvd) Where Not Exists(Buyer&Company,Buyer&Company);