Problem in load when i am using variable

[#1]

hi all.

I have 2 problem. please find the attached file.

Help me please.

[SunilChauhan]

Let vTest = Month(MonthStart(today()- day(today())-2)) & '.xlsx';

Tab1:
LOAD Month,
     Year,
     PrimaryKey,
     Year&''&Month&''&PrimaryKey as UniqueKey,
     Amt
FROM
//C:\Users\Home\Downloads\'$(vTest)'

(ooxml, embedded labels, table is Sheet1);
//(ooxml, embedded labels, table is Sheet1)

Load * ,
1 as junk
resident Tab1
where not exists(UniqueKey,null());
drop table Tab1;

[#3]

Hi Usha,

You can just use * to load all the files from a folder. But you nesure you havent copied same month data with different file names to avoid duplication of data.

LOAD Month,

     Year,

     PrimaryKey,

     Year&''&Month&''&PrimaryKey as UniqueKey,

     Amt

FROM

C:\Users\Home\Downloads\*.xlsx

(ooxml, embedded labels, table is Sheet1);

dont use any where clause and ensure you havent copied duplicates of data files in that folder.

Cheer.