Concatenate uncommon QVDs from different folders

[dmohanty]

Hi Friends,

I have a scenario to concatenate the QVDs present in two folders and store them in a new folder. Both folders have some set of same QVDs with different structures. Those will be concatenated easily. No Problem. But the QVDs that are present in one folder but not in other folder and vice-versa, are not being stored in the destination folder. "Table Not Found" error arises. I really need to store these uncommon QVDs as well in the concatenated folder. How to deal with this scenario? Any IF...ELSE Condition required? Please revise the code below:

let qvdpath = 'C:\Users\XYZ\Desktop';

for each qvd_file in FileList('$(qvdpath)\MAP\*')

let QVDName = subfield('$(qvd_file)','\',6);

$(QVDName):

load

*

FROM

$(qvdpath)\MAP\$(QVDName)

(qvd);

next

for each qvd_file in FileList('$(qvdpath)\HPG\*')

let QVDName = subfield('$(qvd_file)','\',6);

Concatenate($(QVDName))

load

*

FROM

$(qvdpath)\HPG\$(QVDName)

(qvd);

store $(QVDName) into C:\Users\XYZ\Desktop\Test QVDs\$(QVDName);

drop Tables $(QVDName);

next

EXIT Script ;

[dmohanty]

Any help with some examples?  Regards.

[Jason_Michaelides]

The problem here is the files in the second folder that are not in the first have no table to be concatenated to. Files in the first folder but not in the second are being loaded but not stored as the STORE function is part of the second folder loop.

I haven't quite got the time right now to work out the full script, but I think you need to utilise the TableName() and NoOfTables() functions in a loop.  Certainly move the STORE command into a new loop using these (z = 0 to NoOfTables())

Hope this helps,

Jason

[dmohanty]

Hi Jason, Thanks for the reply. Could you please let me know where to place the FOR loop using the functions you have mentioned? Is it something like below: If YES, please let me know how to use it.

FOR z = 0 to NoOfTables()

  LET vTableName = TableName($(z)-1);

  LET vOutfile = '$(vTableName).qvd';

  STORE [$(vTableName)] INTO [$(vOutfile)] (qvd);

NEXT i

[Jason_Michaelides]

I'm afraid loop syntax is not my forte. What you have looks to be along the right lines - I would just play around with it until I got it right! I'll have a play later if I can.

[jonathandienst · Accepted solution]

Hi

If I have properly understood you, this should do the trick:

Let qvdpath = 'C:\Users\XYZ\Desktop';
For Each qvd_file in FileList('$(qvdpath)\MAP\*')
          Let QVDName = SubField(SubField('$(qvd_file)','\',-1), '.', 1);
          $(QVDName):
          LOAD *
          FROM [$(qvdpath)\MAP\$(QVDName)] (qvd);           
Next
For Each qvd_file in FileList('$(qvdpath)\HPG\*')
          Let QVDName = SubField(SubField('$(qvd_file)','\',-1), '.', 1);
          If Alt(NoOfRows('$(QVDName)'), 0) > 0 Then
                    Concatenate($(QVDName)) 
                    LOAD * 
                    FROM [$(qvdpath)\HPG\$(QVDName)] (qvd);
          Else
                    $(QVDName):
                    LOAD *
                    FROM [$(qvdpath)\MAP\$(QVDName)] (qvd);
          End If 
Next
For z = 1 to NoOfTables()
          Let vTableName = TableName(z);
          Let vOutfile = vTableName & '.qvd';
          STORE [$(vTableName)] INTO [$(qvdpath)\Test QVDs\$(vOutfile)] (qvd);
          Drop Table [$(vTableName)];
Next 
EXIT Script ;

Hope that helps

Jonathan

[Jason_Michaelides]

Jonathan,

I was on the train trying to write a reply to this thread but the signal wasn't good enough to post it.  Anyway, it went something like:

"Sorry, but loop syntax isn't really my forte. Try reaching out to someone like Jonathan Dienst who has helped many people on this forum with loops (and other stuff!)"

Your name sprang to mind first!  Your solution above is what I was thinking of but it would have taken me a lot of trial and error to get the syntax right...nice one

Jason

[dmohanty]

Hey 'J' buddies

Thanks a lot in trying out ur hands on this LOOP scenario. The QVDs are concatenating fine. Its a learning experience for me.

However Jonathan,

Can u please let me know , what exactly this section doing? As here I noticed that some of the QVDs  are not being stored, however these are created and concatenated. Few QVDs are stored but those are not stored coming like this in log files.

Next

1/30/2013 3:25:03 PM: 0157    Let vTableName = TableName(z)

1/30/2013 3:25:03 PM: 0158    Let vOutfile = vTableName

1/30/2013 3:25:03 PM: 0159    STORE [] INTO

1/30/2013 3:25:03 PM:         Error: Table not found

1/30/2013 3:25:04 PM: 0161    Drop Table []

1/30/2013 3:25:04 PM:         Error: Table not found

1/30/2013 3:25:05 PM: 0162  Next

[dmohanty]

Hi Jonathan/Jason,

Just to test with I started with 1 QVD in both folders and its getting concatenated and stored. But when I am storing 2 QVDs, only one is being concatenated and other one showing Table Not Found error. Similarly if I am testing with 5 QVDs in both folders, only two are getting stored, rest three are showing errors. Error in the Log File is like below: Please help.

Next

1/30/2013 3:25:03 PM: 0157    Let vTableName = TableName(z)

1/30/2013 3:25:03 PM: 0158    Let vOutfile = vTableName

1/30/2013 3:25:03 PM: 0159    STORE [] INTO

1/30/2013 3:25:03 PM:         Error: Table not found

1/30/2013 3:25:04 PM: 0161    Drop Table []

1/30/2013 3:25:04 PM:         Error: Table not found

1/30/2013 3:25:05 PM: 0162  Next

[Jason_Michaelides]

Try changing:

Let vTableName = TableName(z);

into

Let vTableName = TableName($(z));