Loop issue

[sumanta12]

Hi All,

I have a data like below:

Date	Name
17-Aug-2020	Phillip Summers
18-Aug-2020	Quamar Tran
19-Aug-2020	Daquan Gray
20-Aug-2020	Nasim Rodriguez
21-Aug-2020	Ryder Bryant
22-Aug-2020	Galvin Hays
23-Aug-2020	Austin Navarro
24-Aug-2020	Dieter Padilla
25-Aug-2020	Nissim Vargas
26-Aug-2020	Calvin Rosales
27-Aug-2020	Lance Hensley
28-Aug-2020	Ethan Odonnell
29-Aug-2020	Oscar Harmon
30-Aug-2020	Ira Moore
31-Aug-2020	James Mitchell

Now I want to load data for each date and store it in each qvd and I have written the below script:

TMP_TABLE:
LOAD
DATE(Date,'DD-MMM-YYYY') AS Date,
Name
FROM
[C:\Users\smandal\Downloads\Test.xls]
(biff, embedded labels, table is Worksheet$);

FOR i =0 TO (NOOFROWS('TMP_TABLE')-1)

LET v_data = PEEK('Date',i,'TMP_TABLE');

NOCONCATENATE
TABLE:
LOAD * RESIDENT TMP_TABLE
WHERE Date = '$(v_data)';

STORE TABLE INTO C:\Users\smandal\Downloads\$(v_data)_TEST.QVD(QVD);

NEXT i

But it is not working. The code is generating qvd from 17-Aug-2020 to 31-Aug-2020 but data in the qvd is same for all.

Please help.

[Kushal_Chawda · Accepted solution]

try below

TMP_TABLE:
LOAD distinct 
floor((Date) AS Date,
Name
FROM
[C:\Users\smandal\Downloads\Test.xls]
(biff, embedded labels, table is Worksheet$);

FOR i =0 TO (NOOFROWS('TMP_TABLE')-1)

LET v_data = PEEK('Date',i,'TMP_TABLE');

LET v_data_format = date(PEEK('Date',i,'TMP_TABLE'),'DD-MMM-YYYY');

NOCONCATENATE
TABLE:
LOAD * RESIDENT TMP_TABLE
WHERE floor(Date) = '$(v_data)';

STORE TABLE INTO C:\Users\smandal\Downloads\$(v_data_format)_TEST.QVD(QVD);

DROP Table TABLE;

NEXT i