topic How to add data in excel file for Qlikview? in QlikView

How to add data in excel file for Qlikview?

Wed, 25 Nov 2020 16:16:04 GMT

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 ;

Re: How to add data in excel file for Qlikview?

felipedl — Thu, 31 Aug 2017 17:35:47 GMT

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.

Re: How to add data in excel file for Qlikview?

Fri, 01 Sep 2017 08:46:50 GMT

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?

Re: How to add data in excel file for Qlikview?

Anonymous — Fri, 01 Sep 2017 10:58:01 GMT

What is the error?

Re: How to add data in excel file for Qlikview?

Mon, 04 Sep 2017 15:02:17 GMT

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)

Re: How to add data in excel file for Qlikview?

Anonymous — Mon, 04 Sep 2017 18:09:03 GMT

You have missed ' , ' after Current_Month as Net_BAU

Add comma and reload.

Hope it helps!!