Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

sravan_v007
New Contributor III

Looping through excel rows..

Hi All,

I am trying to pull table structure from excel, instead of mentioning each field name at the Script level.

To avoid changes at script level, if any new fields add in to the table.

table structure in excel: In the below table 1st column will be field name and 2nd will be Alias name.

 

MetricID,MetricID,
MetricName,    MetricName,
MetricCode,    MetricCode,
Quadrants,    Quadrants,
Cellformat,    Cellformat,
OrderID    OrderID

.Script:

Set vQVD='\\..\QVD\';

refMetric_Fields:
LOAD FieldName,
AsName
FROM
[\\...\Excel Tables.xls]
(
biff, embedded labels, table is tbl_BI_Fact$);

LET vTableCount = NoOfRows('refMetric_Fields');

For vi =0 to $(vTableCount)-1
LET vFieldName = Peek('FieldName',$(vi),'refMetric_Fields');
LET vAsName = Peek('AsName',$(vi),'refMetric_Fields');

refMetric:
LOAD
$(vFieldName) as $(vAsName)
FROM $(vQVD)D_TABLE_Metric.qvd
(
qvd);

NEXT

DROP Table refMetric_Fields;

which is giving me below error....

Syntax error, missing/misplaced FROM:

refMetric:

Please help me with the issue.

Thanks,

Regards,

Sravan.

4 Replies
silambarasan130
Contributor II

Re: Looping through excel rows..

Hi,

Can you please share the source file.

or else you can refer the below code.

SheetNameFile:

LOAD

    SheetName,

    FileName

FROM [lib://Excel File/SheetNameFile.xlsx] (ooxml, embedded labels, table is Sheet1);

For i = 0 to NoOfRows('SheetNameFile')-1

       LET vExcelSheets = PEEK('SheetName',i,'SheetNameFile');

       LET vFileName = PEEK('FileName',i,'SheetNameFile');

      

Target_Temp:

LOAD

      *

FROM [lib://Excel File/$(vFileName).xlsx]

(ooxml, embedded labels, table is '$(vExcelSheets)');

NEXT i

Drop Table SheetNameFile;

sravan_v007
New Contributor III

Re: Looping through excel rows..

Hi,

Thanks for replay..

Please find sample data format.

Metric table:

   

MetricIDMetricNameMetricCodeQuadrant
E123AS123People
B345DF234People
C456FG345Finace
D567HJ456HR

I have multiple tables like above, with field names and corresponding values.

Some times the field names has to replace and there will be scenarios where new fields will be added to table.

So, instead of loading like below..

LOAD MetricID,
MetricName,
MetricCode as MetricID,
Quadrant
FROM
[\\svrin000mbp06.global.anz.com\vs3$\Desktop\Sample Data.xlsx]
(
ooxml, embedded labels, table is Fact);

Want to take field names in excel and loop through the rows and save the field names.

   

FieldNameAsName
MetricIDMetricID
MetricNameMetricName
MetricCodeMetricID//For example
QuadrantQuadrant

Regards,

Sravan.

silambarasan130
Contributor II

Re: Looping through excel rows..

Hi,

Instead of taking like below,

LOAD MetricID,
MetricName,
MetricCode as MetricID,
Quadrant
FROM
[\\svrin000mbp06.global.anz.com\vs3$\Desktop\Sample Data.xlsx]
(
ooxml, embedded labels, table is Fact);


you can use,

LOAD *

[\\svrin000mbp06.global.anz.com\vs3$\Desktop\Sample Data.xlsx]
(
ooxml, embedded labels, table is Fact);


If you want the exact query, better you can create a sample data excel file and share it.

Re: Looping through excel rows..

Here you will find various methods to create such a load-statement:

Re: Interesting challenge: How to create a real table from a list of field names?

- Marcus