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?

[realpixel]

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?

[Nicole-Smith]

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;

[Nicole-Smith]

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.

[realpixel]

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.

[Nicole-Smith]

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

[realpixel]

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;

[Nicole-Smith]

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

[realpixel]

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;

[Nicole-Smith]

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.

[realpixel]

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