Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
jagan
Luminary Alumni
Luminary Alumni

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.

View solution in original post

15 Replies
jagan
Luminary Alumni
Luminary Alumni

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
Master II
Master II

hi,

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

HTH

Sushil

jagan
Luminary Alumni
Luminary Alumni

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.

renjithpl
Specialist
Specialist

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
Author

ANY ONE??????

anbu1984
Master III
Master III

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

Anonymous
Not applicable
Author

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
Author

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
Master III
Master III

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