Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

vchuprina
New Contributor III

Store data

Hi guys,

I receive files with data in different sheet, see below name of columns in the first sheet

ModelCodeColorSizeSalesPrice

and second

ModelCodeColorSales STDPrice

They have four columns with the same name.

Could you please advise how can I load data in one output.

6 Replies
Not applicable

Re: Store data

The best approach is talk to source of file creator why they generated the filed names differently. Try to correct field names discrepancy on their end.

Otherwise, you have manually create Field Names Mapping table & use the Rename Fields using Mapping method.

The Field Names mapping should be maintained by you manually or Look for other options how to automate the creation of Field names mapping.

vchuprina
New Contributor III

Re: Store data

Thank you for response.

In output I want to see following columns with data.

Model       Code       Color       Size       Sales       Sales STD      Price

So is this possible to put data in one output if tables have different columns number and different data in columns, for example: first sheet doesn't contain 'Size' column, but has 'Sales STD' and second sheet conversely.

Not applicable

Re: Store data

Are trying to join the these data files ?

Not applicable

Re: Store data

Please provide the sample data & desired o/p so community can help you.

Not applicable

Re: Store data

I'd recommend keeping "Code" in both tables and don't load "Model" and "Color" from one of the tables because if my guess is right, it is redundant data.

1st table:

ModelCodeColorSizeSalesPrice

2nd table:

ModelCodeColorSales STDPrice

The field "Code" will link the data from both tables.

vchuprina
New Contributor III

Re: Store data

Ok. As i said before data comes in one file. Usually files have two sheets one with Last Week data (Sheet 1) and one with STD data (Sheet 2).

So I should create third table that will be contains all columns?

See my script below:

IF( WildMatch(Upper(vSheetName), '*Sheet2*') AND NOT WildMatch(vSheetName,'*WEEK*','*database*', '*_xlnm*') ) THEN

DataLW:

LOAD

FileName() as FILE_NAME,

     '$(vSheetName)' as SHEET_NAME,

     Date(SubField('$(vWKEND)',':',2),'YYYY.MM.DD') AS WEEK_ENDING_DATE,

     SubField('$(vSeason)',':',-1) AS SEASON,

     Model AS STYLE,

     Colour AS COLOR_DESC,

     Size AS SIZE,

     [Sales, pcs] AS SLS_U,

     [Price, €] AS CURRENT_PRICE

FROM

[$(vFile)]

(biff, embedded labels, header is $(vRowStart) lines, table is [$(vSheetName)$])

Where Size <> ''

;

ELSE


DataSTD:

LOAD

FileName() as FILE_NAME,

     '$(vSheetName)' as SHEET_NAME,

     Date(SubField('$(vWKEND)',':',2),'YYYY.MM.DD') AS WEEK_ENDING_DATE,

     SubField('$(vSeason)',':',-1) AS SEASON,

     Model AS STYLE,

     Colour AS COLOR_DESC,

     pcs AS SHIPMENTS_U,

     pcs1 AS STD_SLS_U,

     [€] AS CURRENT_PRICE

FROM

[$(vFile)]

(biff, embedded labels, header is $(vRowStart) lines, table is [$(vSheetName)$])

Where IsNum(€)

;

ENDIF

ENDIF


NEXT

Drop Table Temp_Tables;

NEXT

STORE ....