topic Re: Store data in QlikView

Store data

vchuprina — Fri, 14 Aug 2015 15:31:22 GMT

Hi guys,

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

Model

Code

Color

Size

Sales

Price

and second

Model

Code

Color

Sales STD

Price

They have four columns with the same name.

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

Re: Store data

Fri, 14 Aug 2015 15:38:03 GMT

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.

Re: Store data

vchuprina — Fri, 14 Aug 2015 17:06:29 GMT

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.

Re: Store data

Fri, 14 Aug 2015 17:12:46 GMT

Are trying to join the these data files ?

Re: Store data

Fri, 14 Aug 2015 17:13:21 GMT

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

Re: Store data

Sun, 16 Aug 2015 16:20:33 GMT

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:

Model

Code

Color

Size

Sales

Price

2nd table:

Model

Code

Color

Sales STD

Price

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

Re: Store data

vchuprina — Mon, 17 Aug 2015 12:11:09 GMT

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

Drop Table Temp_Tables;

STORE ....