Import Excel specific value

[realpixel]

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?

[Nicole-Smith]

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

[realpixel]

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?

[Nicole-Smith]

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.

[realpixel]

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.

[Nicole-Smith]

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;

[realpixel]

My chart return no value.

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

to

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?

[Nicole-Smith]

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?

[realpixel]

Find attached my report.

[Nicole-Smith]

In Data2 try changing

HMIMMAT1 & '|' & HMDATE1 as CompositeKey

to

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