topic Re: Re: Re: Import Excel specific value in QlikView

Import Excel specific value

realpixel — Mon, 25 Nov 2013 17:16:48 GMT

Hello,

I loaded the Excel file in my QV report with the following value

HMIMMAT1 LOCATIONS HMDATE1
546GJ6        OKI                 18/11/2013
483GN6        OKI                 21/11/2013
483GN6        OKI                 15/11/2013

This Excel file must be filled every day by a user, the primary key is done with "HMDATE1" same field with my calendar.

I added "LOCATIONS" dimension in my chart.

My problem is when I select the date 18/11/2013 from my calendar I have the following result

HMDATE1    HMIMMAT       LOCATIONS
15/11/2013         483FG6          OKI
15/11/2013 483GN6         OKI
15/11/2013         483GN6         OKI
15/11/2013   484GP6 OKI
15/11/2013        488GP6            OKI
15/11/2013 490GP6 OKI
15/11/2013        490GP6              OKI
15/11/2013         491GP6                    OKI
15/11/2013 491GP6             OKI
15/11/2013 491GP6             OKI
15/11/2013 492GP6              OKI
15/11/2013          492GP6           OKI
15/11/2013          507FJ6 OKI
15/11/2013 508FJ6 OKI
15/11/2013        546GJ6     OKI
15/11/2013 546GJ6 OKI
15/11/2013 673FV6 OKI
15/11/2013   674HH6 OKI
15/11/2013 674HH6 OKI
15/11/2013    682KF4 OKI
15/11/2013 684GA6 OKI
15/11/2013 701HF6 OKI
15/11/2013 701HF6 OKI

I need to have "OKI" in "LOCATIONS" only if there a value in field HMIMMAT1 (file Excel) match with the field HMIMMAT and if there a date in te field HMDATE1 from Excel file

Someone have an idea? Maybe an expression?

Re: Import Excel specific value

Nicole-Smith — Mon, 25 Nov 2013 17:48:31 GMT

You need to create a composite key in the load script against the DATE and MAT fields (see attached).

Re: Import Excel specific value

realpixel — Mon, 25 Nov 2013 21:29:27 GMT

Thank you Nicole for your answer.

From example.qvw

1 - Is not possible to use an external file like a Excel file with the same command "Compositekey"? Because, these values must be updated every day by a user.

DATA1:

LOAD *, HMIMMAT1 & '|' & HMDATE1 as CompositeKey INLINE [
HMIMMAT1,LOCATIONS,HMDATE1

546GJ6,OKI,18/11/2013
483GN6,OKI,21/11/2013
483GN6,OKI,15/11/2013
];

2 - For DATA2, I use an ODBC driver to connect on SQL DB, is not possible to use also the command "Compositekey" in this case?

Re: Import Excel specific value

Nicole-Smith — Tue, 26 Nov 2013 13:30:10 GMT

CompositeKey isn't a command--it is just a field that we are creating by concatenating your two other fields together. You should be able to use it with both Excel files and SQL DB.

If you want help with writing the code for it, please let me see your current load script for these two tables.

Re: Import Excel specific value

realpixel — Tue, 26 Nov 2013 14:04:39 GMT

Hello,

find under the script loaded in main

Load HMPARC,
HMCONT,
HMCISO,
Pick(WildMatch([HMCISO],'4*','2*'),'40','20') as ISO,
HMMOUV,
HMCYCLE,
HMVOUP,
HMTRANSI,
HMSENS,
HMIMMAT,
HMPBRUT,
HMHEURCR,
Time(time#([HMHEURCR],'hhmmss'),'hh:mm:ss') as HEURE,
HMUSER2,
Pick(WildMatch([HMUSER2],'1','2','6')+1,'VIDES','ZU','ZP','ZL') as ZONE,
HMMANUT,
HMAGENT,
HMDATE,

if(Right([HMDATE],2) <> 00, Date(Date#([HMDATE],'YYYYMMDD'),'DD/MM/YYYY')) as HMDATE1,
HMEXPLTC,
HMPOSIT,
HMPOSIT as [LIEU POSITIONNEMENT],

HMNDOC,

HMESCALE,
HMLIGNE,
HMNAVIRE;

SELECT*
FROM DWHCG.dbo.livraison where (HMMOUV='ENVC' or HMMOUV='LIPN' or HMMOUV='DEPO' or HMMOUV='SOVC')

and second script for Excel file

load HMIMMAT1,
LOCATIONS,
HMDATE1

HMIMMAT1,
FROM
C:\Qlikview_Rapports\LOCATIONS.xlsx
(ooxml, embedded labels, table is Feuil1);

There are a script for calender that use "HMDATE1" like primary key.

Re: Re: Import Excel specific value

Nicole-Smith — Tue, 26 Nov 2013 14:23:30 GMT

Something along these lines should give you what you need (text in red is what I changed):

Data1:

Load HMPARC,

HMCONT,

HMCISO,

Pick(WildMatch([HMCISO],'4*','2*'),'40','20') as ISO,

HMMOUV,

HMCYCLE,

HMVOUP,

HMTRANSI,

HMSENS,

HMIMMAT,

HMPBRUT,

HMHEURCR,

Time(time#([HMHEURCR],'hhmmss'),'hh:mm:ss') as HEURE,

HMUSER2,

Pick(WildMatch([HMUSER2],'1','2','6')+1,'VIDES','ZU','ZP','ZL') as ZONE,

HMMANUT,

HMAGENT,

HMDATE,

if(Right([HMDATE],2) <> 00, Date(Date#([HMDATE],'YYYYMMDD'),'DD/MM/YYYY')) as HMDATE1,

HMEXPLTC,

HMPOSIT,

HMPOSIT as [LIEU POSITIONNEMENT],

HMNDOC,

HMESCALE,

HMLIGNE,

HMNAVIRE,

HMIMMAT & '|' & if(Right([HMDATE],2) <> 00, Date(Date#([HMDATE],'YYYYMMDD'),'DD/MM/YYYY')) as CompositeKey;

SELECT*

FROM DWHCG.dbo.livraison where (HMMOUV='ENVC' or HMMOUV='LIPN' or HMMOUV='DEPO' or HMMOUV='SOVC')

Data2:

load HMIMMAT1,

LOCATIONS,

HMDATE1,

HMIMMAT1 & '|' & HMDATE1 as CompositeKey

FROM

C:\Qlikview_Rapports\LOCATIONS.xlsx

(ooxml, embedded labels, table is Feuil1);

DROP FIELDS HMDATE1, HMIMAT FROM Data1;

Re: Re: Re: Import Excel specific value

realpixel — Tue, 26 Nov 2013 14:55:57 GMT

My chart return no value.

I have just modify
DROP FIELDS HMDATE1, HMIMAT FROM Data1;

DROP FIELDS HMDATE1, HMIMMAT FROM Data1;

maybe the problem is due to date (HMDATE1) format

The compositekey in data2 appears like that

546GJ6|41596
483GN6|41599
483GN6|41593

The value 41596, 41599 and 41593 must be in the format date like that DD/MM/YYYY, no?

Re: Import Excel specific value

Nicole-Smith — Tue, 26 Nov 2013 15:01:20 GMT

They don't need to be in the DD/MM/YYYY format--the date just needs to have the same format in the CompositeKey in BOTH tables.

Can you post your .qvw?

Re: Re: Import Excel specific value

realpixel — Tue, 26 Nov 2013 15:10:29 GMT

Find attached my report.

Re: Import Excel specific value

Nicole-Smith — Tue, 26 Nov 2013 15:19:40 GMT

In Data2 try changing

HMIMMAT1 & '|' & HMDATE1 as CompositeKey

HMIMMAT1 & '|' & date(HMDATE1, 'DD/MM/YYYY') as CompositeKey

Re: Import Excel specific value

realpixel — Tue, 26 Nov 2013 16:28:42 GMT

Thank you, it's works now but if the Excel file is not filled for other days, there are no values loaded.

For example, just for test we have filled the Excel file only for the days 15/11/2013, 15/11/2013 and 21/11/2013

And on QV chart I have a values only for the date 15/11/2013, 15/11/2013 and 21/11/2013.

Is not possible to have all date even if the Excel file is not filled?

Re: Re: Import Excel specific value

Nicole-Smith — Tue, 26 Nov 2013 16:39:02 GMT

I dropped HMDATE1 from Data1 so that what is kept is from Data2, but if the entire Date list is in Data1, the drop statements should then read:

DROP FIELD HMIMAT FROM Data1;

DROP FIELD HMDATE1 FROM Data2;

Re: Import Excel specific value

Nicole-Smith — Tue, 26 Nov 2013 16:39:50 GMT

If you need dates from both tables, you will need to create a master calendar from both of them. You can find many posts on how to do so on the forums.

Re: Import Excel specific value

realpixel — Tue, 26 Nov 2013 16:46:46 GMT

If I use these command

DROP FIELD HMIMAT FROM Data1;

DROP FIELD HMDATE1 FROM Data2;

I have the following message when I reload my report

One or more loops have been detected in your database structure. Loops may cause ambiguous results and should therefore be avoided. QlikView will cut the loop(s) by setting one or more tables as loosely coupled. Settings for loosely coupled tables can be modified after script execution in the tables page of the document properties dialog.

Re: Import Excel specific value

Nicole-Smith — Tue, 26 Nov 2013 16:49:34 GMT

You're going to need to see what fields cause the loops and drop them from one of the tables.

Re: Import Excel specific value

realpixel — Tue, 26 Nov 2013 17:12:04 GMT

I dropped CompositeKey, now there no loops but I'm in the same configuration initially, all value "LOCATIONS" appears with "OKITO"

I'm in the same configuration that initially

FIELDS HMIMMAT FROM Data1;

DROP FIELD HMDATE1 FROM Data2;

DROP FIELD CompositeKey FROM Data2;

Re: Import Excel specific value

Nicole-Smith — Tue, 26 Nov 2013 17:13:11 GMT

You need the CompositeKey to link your data properly. Drop the other field that was causing the loop.

Re: Import Excel specific value

realpixel — Tue, 26 Nov 2013 17:32:10 GMT

I dropped HMDATE1 from DATA1, no loops, but I have the problem all value in LOCATIONS appears with OKITO only for days 15/11/2013, 15/11/2013 and 21/11/2013. All other days are blank.

DROP FIELDS HMIMMAT FROM Data1;

DROP FIELD HMDATE1 FROM Data2;

DROP FIELDS HMDATE1 FROM Data1;

Re: Import Excel specific value

Nicole-Smith — Tue, 26 Nov 2013 17:45:08 GMT

I don't understand what you're doing or what you're trying to do. You need to go back to my original post (Re: Import Excel specific value)--what you need is a composite key made up of your MAT and DATE field. Once you have the composite key working properly, your calculations will be correct. If you don't understand how composite keys work, there are plenty of posts on the forums that should be able to help you.

Re: Import Excel specific value

realpixel — Wed, 27 Nov 2013 11:53:56 GMT

Sorry I misspoke.

Under a part of CompositeKey field

731HF6|15/11/2013

710HF6|15/11/2013

708HF6|15/11/2013

701HF6|15/11/2013

316|15/11/2013

285FG6|15/11/2013

298|15/11/2013

705HF6|15/11/2013

388|15/11/2013

411213|15/11/2013

393|15/11/2013

483FG6|15/11/2013

If the field HMDATE1 is blank in Excel file, it's possible to have in HMIMMAT1 or a new field, the value before the "|"?

For example, the file Excel contains the value Under.

In QV chart in HMIMMAT1 field only the 546GJ6/483GN6 appears. Now I need to have the value 038FE6 and others contains in this field is the value LOCATIONS or HMDATE1 are null

HMIMMAT1	LOCATIONS	HMDATE1
546GJ6	OKITO	18/11/2013
483GN6	OKITO	21/11/2013
483GN6	OKITO	15/11/2013
038FE6
135DE6
731HF6