Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
tamilarasu

Join Table

Hi,

I am new to qlikview.I want to add a specific cell value as column for all data in that file. I have found a solution but when i put the same code in loop i got wrong result.I am not sure where i am wrong.

My output should be like below screenshot

Capture12.PNG

but i got the below output.

Capture.PNG

Script i tried

sub ScanFolder(Root)

          for each FileExtension in 'xlsx'

                    for each FoundFile in filelist( Root & '\*.' & FileExtension)

FileData:     

LOAD Sno,

     Cust,

     Prod,

     [Sold Qty],

     Price

FROM

[$(FoundFile)] (ooxml, embedded labels, table is Sheet1, filters(

Remove(Row, RowCnd(Interval, Pos(Top, 1), Pos(Top, 1), Select(1, 0)))

));

                             

LOAD

     B as Country

FROM

[$(FoundFile)] (ooxml, explicit labels, table is Sheet1)                             

WHERE RecNo() =1;

next FoundFile

          next FileExtension

end sub

Call ScanFolder('C:\Users\Tamilarasu.Nagaraj\Desktop\New folder (2)') ;

Attached sample files. Can anyone help me.

Thank you in advance.

Kind Regards,

Tamil

1 Solution

Accepted Solutions
Henric_Cronström

You need a link between Country and the rest of the data. E.g.

for each FileExtension in 'xlsx'

   for each FoundFile in filelist( '*.' & FileExtension)

   Country:

   LOAD

         B as Country

      FROM [$(FoundFile)] (ooxml, explicit labels, table is Sheet1)

      WHERE RecNo() =1;

      Let vCountry = Peek('Country',-1,'Country');

      FileData:     

      LOAD

         '$(FoundFile)' as FileName,

         '$(vCountry)' as Country,

         Sno,

         Cust,

         Prod,

         [Sold Qty],

         Price

      FROM [$(FoundFile)] (ooxml, embedded labels, header is 1 lines, table is Sheet1);

   next FoundFile

next FileExtension

HIC

View solution in original post

11 Replies
marcarreras
Specialist
Specialist

Tamil,

Read Country in a variable and load it with the rest of data:

sub ScanFolder(Root)

          for each FileExtension in 'xlsx'

                    for each FoundFile in filelist( Root & '\*.' & FileExtension)


cTable:

LOAD

    B as Country

FROM

[$(FoundFile)] (ooxml, explicit labels, table is Sheet1)                           

WHERE RecNo() =1;


LET vCountry = Peek('Country', -1, 'cTable');

DROP Table cTable;


FileData:   

LOAD $(vCountry) as Country,

    Sno,

    Cust,

    Prod,

    [Sold Qty],

    Price

FROM

[$(FoundFile)] (ooxml, embedded labels, table is Sheet1, filters(

Remove(Row, RowCnd(Interval, Pos(Top, 1), Pos(Top, 1), Select(1, 0)))

));

                           

next FoundFile

          next FileExtension

end sub

Call ScanFolder('C:\Users\Tamilarasu.Nagaraj\Desktop\New folder (2)') ;

Henric_Cronström

You need a link between Country and the rest of the data. E.g.

for each FileExtension in 'xlsx'

   for each FoundFile in filelist( '*.' & FileExtension)

   Country:

   LOAD

         B as Country

      FROM [$(FoundFile)] (ooxml, explicit labels, table is Sheet1)

      WHERE RecNo() =1;

      Let vCountry = Peek('Country',-1,'Country');

      FileData:     

      LOAD

         '$(FoundFile)' as FileName,

         '$(vCountry)' as Country,

         Sno,

         Cust,

         Prod,

         [Sold Qty],

         Price

      FROM [$(FoundFile)] (ooxml, embedded labels, header is 1 lines, table is Sheet1);

   next FoundFile

next FileExtension

HIC

View solution in original post

simenkg
Partner
Partner

sub ScanFolder(Root)

FinalTable:

load 'Dummy' as Dummy autogenerate 1;


          for each FileExtension in 'xlsx'

                    for each FoundFile in filelist( Root & '\*.' & FileExtension)

FileData:    

LOAD Sno,

     Cust,

     Prod,

     [Sold Qty],

     Price

FROM

[$(FoundFile)] (ooxml, embedded labels, table is Sheet1, filters(

Remove(Row, RowCnd(Interval, Pos(Top, 1), Pos(Top, 1), Select(1, 0)))

));


outer join (FileData)                           

LOAD

     B as Country

FROM

[$(FoundFile)] (ooxml, explicit labels, table is Sheet1)                            

WHERE RecNo() =1;


concatenate(FinalTable)

Load * resident FileData;

Drop table FileData;



next FoundFile

          next FileExtension

Drop Field Dummy;

end sub

Call ScanFolder('C:\Users\Tamilarasu.Nagaraj\Desktop\New folder (2)') ;

tamilarasu
Author

Hi Marc.

I got the below error while executing the above code. when I click ok button, it again shows error message for another country.

Capture.PNG

tamilarasu
Author

Henric: Your solution is working fine.

tamilarasu
Author

Simen: Your solution also working fine but I got an error message. Could you please tell me why we are using Dummy field here. Thank you

Capture.PNG

simenkg
Partner
Partner

The Dummy field is used to initialize the table. You cant create an empty table, as far as I know.

simenkg
Partner
Partner

Move the Drop Field Dummy; Statement after the Next statements and it should work (I have fixed it in my original answer).

marcarreras
Specialist
Specialist

Sorry Tamil, I missed commas

...

LOAD '$(vCountry)' as Country,

...

Marc.