topic Re: Formatting of import data in QlikView

Formatting of import data

stefanie_a — Mon, 16 Aug 2021 15:36:55 GMT

Hi,

I'm relatively new to QlikView and need your help.

My problem is best explained by an example (see file below).

I need to format data so that values from one column can be selected as different dimensions.

Unfortunataly in my raw data the dimensions "Leistung" and "Fall" are displayed in the same column.

"Leistung" always begins with the letter "J".

"Fall" always has 7 numbers.

How can these two parameters be shown as two different dimensions without formatting manually in Excel?

Thank you in advance!

Best regards,

Stefanie

Re: Formatting of import data

stevejoyce — Mon, 16 Aug 2021 16:14:18 GMT

See if this works for you

//load excel data and flag column 1 if its type Leistung or Fall based on cell value

//source data includes 2 header rows. keep first and remove 2nd.
temp_data_load:
load
*
,If(wildmatch(Leistung, 'J*'),1,2) as column1_type
FROM
[C:\Users\sjoyce\Desktop\Test-Datei Qlik View.xlsx]
(ooxml, embedded labels, table is Data, filters(
Remove(Row, Pos(Top, 2))
))
Where len(Leistung)>0
;

//derive new Leistung value for each row
data:
load *

,If(column1_type=1, Leistung, peek(Leistung_new)) as Leistung_new

resident temp_data_load;

drop table temp_data_load;

//only keep original rows from data that were type Fall
inner join (data)
load distinct
2 as column1_type
autogenerate(1)
;

drop field column1_type;

//Rename fields accurately
rename field Leistung to Fall;
rename field Leistung_new to Leistung;

exit script;

Re: Formatting of import data

stefanie_a — Mon, 23 Aug 2021 14:20:15 GMT

Hi @stevejoyce ,

thank you very much! Your script worked well for me.

But now I have a problem with the number format.

This is how the data looks like. Seems as if there are added 6 digits in every field except in field "Anzahl_ZE".

Do you know how to solve this problem?

Fall,Leistung,Anzahl_ZE,Fälle,Entgelthöhe,Erlössumme

9999999.000000,J0ZE22222,1,1.000000,200.000000,200.000000

9999999.000000,J0ZE33333,1,1.000000,700.000000,700.000000

8888888.000000,J0ZE33333,1,1.000000,700.000000,700.000000

9999999.000000,J0ZE11111,1,1.000000,3000.000000,3000.000000

7777777.000000,J0ZE33333,1,1.000000,700.000000,700.000000

Please let me know if you need the qvw!

Again thanks a lot for your help!

Best regards,

Stefanie

Re: Formatting of import data

Kushal_Chawda — Mon, 23 Aug 2021 14:48:36 GMT

@stefanie_a try below

data: LOAD *, if(IsNum(Fall),Peek(Leistung),Fall) as Leistung FROM [C:\Test-Datei Qlik View.xlsx] (ooxml, embedded labels, table is Data, filters( Remove(Row, Pos(Top, 1)) )) where len(trim(Fall))>0; Final: NoConcatenate LOAD * Resident data where isnum(Fall); DROP Table data;

Re: Formatting of import data

stefanie_a — Tue, 24 Aug 2021 11:54:58 GMT

Hi @Kushal_Chawda ,

thank you for your response!

Unfortunately, it does not change the data format. Values are still multiplied by 1.000.000 when storing data into excel.

When selecting the fields in a diagramm, I changed the data format into decimal numbers and the values are displayed correctly. For the dimension (Fall) I used floor(fall) and it worked correctly.

Do you have another idea how to solve the problem in the script? (I need the excel-export with correct formatting directly from the script.)

Thank you in advance!

Best regards, Stefanie

Re: Formatting of import data

McNulty35 — Thu, 26 Aug 2021 03:38:17 GMT

Analyst's Notebook can import text and spreadsheet data in the following formats. To import data in any of these formats, you need to use an import specification to define the entities that are represented by the data and the links between those entities. An import specification might exist for the format you want to import, or you can create a new specification to interpret your data.

MyPascoConnect

Re: Formatting of import data

stefanie_a — Tue, 31 Aug 2021 12:41:33 GMT

Hi @McNulty35

Where can I set these Specifications?

When Loading the Excel-File I used the following:

temp_data_load: LOAD Leistung, [Anz ZE], Fälle, Entgelthöhe, Erlössumme, If(wildmatch(Leistung, 'J*'),1,2) as column1_type FROM [...\data.xlsx] (ooxml, embedded labels, header is 4 lines, table is [...],filters(Remove(Row, Pos(Top, 2)))) Where len(Leistung)>0 ;

The data should look like this:

Leistung	Fall	Anz ZE	Fälle	Entgelthöhe	Erlössumme
J0ZE11111	9999999	1	1	3.000,00	3.000,00
J0ZE22222	9999999	1	1	200,00	200,00
J0ZE33333	9999999	1	1	700,00	700,00
J0ZE33333	8888888	1	1	700,00	700,00
J0ZE33333	7777777	1	1	700,00	700,00

Can you help?

Thank you in advance!

Best regards, Stefanie

Re: Formatting of import data

stevejoyce — Tue, 31 Aug 2021 12:56:14 GMT

Hi @stefanie_a . I've formatted fields in the first load statement. I'm not familiar with the number formatting for the two fields starting with E, you can adjust the '#,###.#' num format parameter as desired. https://help.qlik.com/en-US/qlikview/May2021/Subsystems/Client/Content/QV_QlikView/Scripting/FormattingFunctions/Num.htm

//load excel data and flag column 1 if its type Leistung or Fall based on cell value

//source data includes 2 header rows. keep first and remove 2nd.
temp_data_load:
load
If(IsNum(Leistung), Num(Leistung), Leistung) as Leistung
,Num([Anz ZE]) as [Anz ZE]
,Num([Fälle]) as [Fälle]
,Num(Entgelthöhe, '#,###.#')as Entgelthöhe
,Num(Erlössumme, '#,###.#') as Erlössumme
,If(wildmatch(Leistung, 'J*'),1,2) as column1_type
FROM
[C:\Users\sjoyce\Desktop\Test-Datei Qlik View.xlsx]
(ooxml, embedded labels, table is Data, filters(
Remove(Row, Pos(Top, 2))
))
Where len(Leistung)>0
;

//derive new Leistung value for each row
data:
load
*

,If(column1_type=1, num#(Leistung), peek(Leistung_new)) as Leistung_new

resident temp_data_load;

drop table temp_data_load;

//only keep original rows from data that were type Fall
inner join (data)
load distinct
2 as column1_type
autogenerate(1)
;

drop field column1_type;

//Rename fields accurately
rename field Leistung to Fall;
rename field Leistung_new to Leistung;

exit script;

Re: Formatting of import data

stefanie_a — Tue, 28 Sep 2021 17:29:04 GMT

Hi @stevejoyce,

thank you very much! This works perfectly!

Re: Formatting of import data

James12 — Thu, 18 Nov 2021 07:05:15 GMT

I have been looking for this for a very long time. myolsd

Re: Formatting of import data

James12 — Thu, 18 Nov 2021 07:06:28 GMT

Even i was looking for the same script. myaccessflorida