Reading variable from field value

[#1]

Hi Guys,

Im having issue here. What i'm try to do is :

1. CheckTable - Load FieldA

2. Tmptable - to load distinct and set FieldA as variable called FieldB

3. set $s, as variable value from Field B

4. Load Mapping table based on set $s

The issue is when i try to load the $s does not recognize the value, instead it reads as one line data . I try to debug and value $s = 01022016,02022016,03022016,04022016....

i'm trying to read the filename based on $s. however it gives me error.

Cannot open file 'D:\..\..\Filename_01032016,02022016,03022016,05022016,06022016,12022016,13022016,14022016,18022016,22022016,24022016,25022016,27022016,29022016.QVD' The system cannot find the file specified.

CheckTable:

LOAD

  Date([FieldA], 'DDMMYYYY') AS [FieldA]

  FROM

  [..\..\..\.xlsx]

tmp:

NoConcatenate

LOAD

  CONCAT(Distinct [FieldA],',') as [[FieldB]]

  Resident CheckTable;

LET vTrxDate1= peek('FieldB',0,'tmp');

LET s ='$(vTrxDate1)';

DROP TABLE tmp;

MapTable:

Mapping

LOAD

    ValueA,

      ValueB

    FROM

  [..\..\Filename_$(s).QVD] (qvd);

NEXT

[Kushal_Chawda · Accepted solution]

may be like this

CheckTable:

LOAD

  Date([FieldA], 'DDMMYYYY') AS FieldA

  FROM

  [..\..\..\.xlsx]

for i=1 to fieldvaluecount('FieldA')

LET S = fieldvalue('FieldA',$(i)) ;

MapTable:

Mapping

LOAD

    ValueA,

      ValueB

    FROM

  [..\..\Filename_$(S).QVD] (qvd);

NEXT i

[#3]

Hi Kushal,

It works perfectly!. Thanks so much

[Kushal_Chawda]

Not a problem