Formatting of import data

[stefanie_a]

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

[stevejoyce]

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;

[stefanie_a]

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

[Kushal_Chawda]

@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;

[stefanie_a]

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

[McNulty35]

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

[stefanie_a]

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

[stevejoyce · Accepted solution]

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/Formatt...

//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;

[stefanie_a]

Hi @stevejoyce,

thank you very much! This works perfectly!

[James12]

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