How to add data in excel file for Qlikview?

[#1]

Hi All

I am trying to get Qlikview to pick up more data from an excel file, unfortunately the original developer has left and I am a Qlikview novice so need help! Basically Qlikview picks up data from the first 6 columns in the excel file. I have added another column in the excel file (BAU release) but Qlikview is not finding this, I think it has something to do with the tables or fields Qlikview loads. Would really appreciate if someone can help.

Thanks

Imran

This is the excel file

This is what is in the data model:

BAU_Extract:

NoConcatenate

LOAD

//LOS,

'Cluster Net BAU' as File_Type,

1 as [Cluster Net BAU Flag],

     IF(wildmatch(Business_Unit,'*National*')>0 ,'Other', ApplyMap('Map_Old_BU',Business_Unit,Business_Unit & '(No Mapping)') ) as Business_Unit_Tables,

     ApplyMap('Map_Old_BU',Business_Unit,Business_Unit & '(No Mapping)') as Business_Unit,

     ApplyMap('Map_Old_Cluster',Cluster,Cluster & '(No Mapping)') as Cluster, 

     ApplyMap('Map_Old_IS',Industry_Sector,Industry_Sector & '(No Mapping)') as Industry_Sector,

     Num(Monthstart($(v_LoadDate))) as Date_KEY,

     Current_Month as Net_BAU

FROM

[$(v_Net_BAU)] //[\\uk\dash\\Net BAU.xlsx]

(ooxml, embedded labels, header is 1 lines, table is BAU)

Where not IsNull(Current_Month) ;

Concatenate (BAU_Extract)

LOAD

//LOS,

'Cluster Net BAU' as File_Type,

1 as [Cluster Net BAU Flag],

     IF(wildmatch(Business_Unit,'*National*')>0 ,'Other', ApplyMap('Map_Old_BU',Business_Unit,Business_Unit & '(No Mapping)') ) as Business_Unit_Tables,

     ApplyMap('Map_Old_BU',Business_Unit,Business_Unit & '(No Mapping)') as Business_Unit,

     ApplyMap('Map_Old_Cluster',Cluster,Cluster & '(No Mapping)') as Cluster, 

     ApplyMap('Map_Old_IS',Industry_Sector,Industry_Sector & '(No Mapping)') as Industry_Sector, 

     Num(Addmonths(Monthstart($(v_LoadDate)),1)) as Date_KEY,

     Next_Month as Net_BAU

FROM

[$(v_Net_BAU)] //[\\uk\dash\Consulting\Internal\DAT Qlik\00 Dashboards\02 Consulting Dashboard\02 Latest Dashboard & Input Files\17b. Net BAU.xlsx]

(ooxml, embedded labels, header is 1 lines, table is BAU)

Where not IsNull(Next_Month) ;

Concatenate ( Cluster_Fact_Table )

LOAD * Resident  BAU_Extract ;

Drop table BAU_Extract ;

[felipedl]

Hi Imran,

In this table

BAU_Extract:

NoConcatenate

LOAD

//LOS,

'Cluster Net BAU' as File_Type,

1 as [Cluster Net BAU Flag],

     IF(wildmatch(Business_Unit,'*National*')>0 ,'Other', ApplyMap('Map_Old_BU',Business_Unit,Business_Unit & '(No Mapping)') ) as Business_Unit_Tables,

     ApplyMap('Map_Old_BU',Business_Unit,Business_Unit & '(No Mapping)') as Business_Unit,

     ApplyMap('Map_Old_Cluster',Cluster,Cluster & '(No Mapping)') as Cluster,

     ApplyMap('Map_Old_IS',Industry_Sector,Industry_Sector & '(No Mapping)') as Industry_Sector,

     Num(Monthstart($(v_LoadDate))) as Date_KEY,

     Current_Month as Net_BAU,

     Next_Month as NextMonth_Net_BAU

FROM

[$(v_Net_BAU)] //[\\uk\dash\\Net BAU.xlsx]

(ooxml, embedded labels, header is 1 lines, table is BAU)

Where not IsNull(Current_Month) ;

You would have to add the other field you created, in this case the Next_Month (its bolded in the code). It's a guess only, but I'm assuming the first table as it states, only extract the data from the Excel file.

Felipe.

[#3]

Hi Felipe

Thank you for the reply. I tried adding 'BAU_release as BAU release name' below the Next_month section and the script failed.

Any ideas on what to try next?

[Anonymous]

What is the error?

[#5]

Syntax error, missing/misplaced FROM:

BAU_Extract:

NoConcatenate

LOAD

'Cluster Net BAU' as File_Type,

1 as [Cluster Net BAU Flag],

     IF(wildmatch(Business_Unit,'*National*')>0 ,'Other', ApplyMap('Map_Old_BU',Business_Unit,Business_Unit & '(No Mapping)') ) as Business_Unit_Tables,

     ApplyMap('Map_Old_BU',Business_Unit,Business_Unit & '(No Mapping)') as Business_Unit,

     ApplyMap('Map_Old_Cluster',Cluster,Cluster & '(No Mapping)') as Cluster, 

     ApplyMap('Map_Old_IS',Industry_Sector,Industry_Sector & '(No Mapping)') as Industry_Sector,

     Num(Monthstart(42977)) as Date_KEY,

     Current_Month as Net_BAU

     BAU_Release as BAU Release name

FROM

[\\uk\dash\Consulting\Internal\DAT Qlik\00 Dashboards\02 Consulting Dashboard\02 Latest Dashboard & Input Files\17b. Net BAU.xlsx]

(ooxml, embedded labels, header is 1 lines, table is BAU)

Where not IsNull(Current_Month)

BAU_Extract:

NoConcatenate

LOAD

'Cluster Net BAU' as File_Type,

1 as [Cluster Net BAU Flag],

     IF(wildmatch(Business_Unit,'*National*')>0 ,'Other', ApplyMap('Map_Old_BU',Business_Unit,Business_Unit & '(No Mapping)') ) as Business_Unit_Tables,

     ApplyMap('Map_Old_BU',Business_Unit,Business_Unit & '(No Mapping)') as Business_Unit,

     ApplyMap('Map_Old_Cluster',Cluster,Cluster & '(No Mapping)') as Cluster, 

     ApplyMap('Map_Old_IS',Industry_Sector,Industry_Sector & '(No Mapping)') as Industry_Sector,

     Num(Monthstart(42977)) as Date_KEY,

     Current_Month as Net_BAU

     BAU_Release as BAU Release name

FROM

[\\uk\dash\Consulting\Internal\DAT Qlik\00 Dashboards\02 Consulting Dashboard\02 Latest Dashboard & Input Files\17b. Net BAU.xlsx]

(ooxml, embedded labels, header is 1 lines, table is BAU)

Where not IsNull(Current_Month)

[Anonymous]

Hi

You have missed ' , ' after Current_Month as Net_BAU

Add comma and reload.

Hope it helps!!