topic Data modeling issue and loop in QlikView

Data modeling issue and loop

Tue, 14 Dec 2010 21:25:30 GMT

Hello everyone!

I have a problem to resolve this issue :

I want to make an analysis of my stocks and my sales. I'd like to know how much day of sales I have in stock. But I can't resolve a loop problem...

Here are my basic datas :


ETB:
LOAD * INLINE [
 ETBCOD, SITE
 EUR, PARIS
 LEO, PARIS
 VSG, PARIS
 FDS, LYON
 FDM, MARSEILLE
];

DEP:
LOAD * INLINE [
 SIGDEP, SITE
 VSG, PARIS
 EFV, PARIS
 FDS, LYON
 EFL, LYON
 FDM, MARSEILLE
 EFM, MARSEILLE
];

SALES:
LOAD * INLINE [
 ETBCOD, CODPRO, SALES
 EUR, 501660, 100
 LEO, 501660, 100
 VSG, 501660, 100
 FDS, 501660, 100
 FDM, 501660, 100
 EUR, L50166, 100
 LEO, L50166, 100
 VSG, L50166, 100
 FDS, L50166, 100
 FDM, L50166, 100
 EUR, 501666, 100
 LEO, 501666, 100
 VSG, 501666, 100
 FDS, 501666, 100
 FDM, 501666, 100
 EUR, L5016P, 100
 LEO, L5016P, 100
 VSG, L5016P, 100
 FDS, L5016P, 100
 FDM, L5016P, 100
];

STK:
LOAD * INLINE [
 SIGDEP, PROSTK, STOCK_VALUE
 EFV, 501660, 200
 VSG, 501660, 200
 EFM, 501660, 200
 FDM, 501660, 200
 EFL, 501660, 200
 FDS, 501660, 200
];

PROD:
LOAD * INLINE [
 CODPRO, PROSTK, PROSTA
 501660, 501660, 501660
 L50166, 501660, 501660
 501666, 501660, 501660
 L5016P, 501660, 501660
];

You can see the data model right here (with a loop) :

In fact, you can see the result that I want in designer :

I tried a lot of combination to do so, but with bad results...

Please, can somebody tell me what would be the best data model in order to reduce the amount of data (I mean in order to not repeat X times the same data in a table)?

Thanks a lot!

Bye!

Data modeling issue and loop

andy — Tue, 21 Dec 2010 06:07:22 GMT

Hi,

In Qlikview (in opposite to a DB) it is ok to have the same data repeated since it is just stored as a pointer to the real value. Do the most you can in the script and make your tables be as few as possible then Qlikview performs the best.

Try this:

//ETB:
DIMENSIONS_TMP:
LOAD * INLINE [
ETBCOD, SITE
EUR, PARIS
LEO, PARIS
VSG, PARIS
FDS, LYON
FDM, MARSEILLE
];

//DEP
LEFT JOIN LOAD * INLINE [
SIGDEP, SITE
VSG, PARIS
EFV, PARIS
FDS, LYON
EFL, LYON
FDM, MARSEILLE
EFM, MARSEILLE
];

DIMENSIONS:
LOAD
ETBCOD&'-'&SIGDEP as %ETB_SIG_KEY,
SITE
Resident DIMENSIONS_TMP;

DROP Table DIMENSIONS_TMP;

SALES_tmp:
LOAD * INLINE [
ETBCOD, CODPRO, SALES
EUR, 501660, 100
LEO, 501660, 100
VSG, 501660, 100
FDS, 501660, 100
FDM, 501660, 100
EUR, L50166, 100
LEO, L50166, 100
VSG, L50166, 100
FDS, L50166, 100
FDM, L50166, 100
EUR, 501666, 100
LEO, 501666, 100
VSG, 501666, 100
FDS, 501666, 100
FDM, 501666, 100
EUR, L5016P, 100
LEO, L5016P, 100
VSG, L5016P, 100
FDS, L5016P, 100
FDM, L5016P, 100
];

//PROD:
LEFT JOIN LOAD * INLINE [
CODPRO, PROSTK, PROSTA
501660, 501660, 501660
L50166, 501660, 501660
501666, 501660, 501660
L5016P, 501660, 501660
];

//STK:
LEFT JOIN LOAD * INLINE [
SIGDEP, PROSTK, STOCK_VALUE
EFV, 501660, 200
VSG, 501660, 200
EFM, 501660, 200
FDM, 501660, 200
EFL, 501660, 200
FDS, 501660, 200
];

DATA:
LOAD
ETBCOD&'-'&SIGDEP as %ETB_SIG_KEY,
CODPRO,
SALES,
PROSTK,
PROSTA,
STOCK_VALUE
Resident SALES_tmp;

DROP Table SALES_tmp;

Data modeling issue and loop

sunil2288 — Tue, 21 Dec 2010 07:51:49 GMT

Hi,

You just Concatenate the ETB and DEP tables as both of the tables sam etype of information as location.

This may be the solution

You need to rename a col in the DEP Table or ETB Table.

Regards

Sunil

Data modeling issue and loop

Tue, 21 Dec 2010 10:48:38 GMT

HI,

you problem is of Circular Loops...... Refer to the user Manuel you will surly get help.

You can rename the fields which is causing to Loop or can use Mapping Load as you tables have only 2 fileds , map them into single table.

Plz let me knw if this solve your problem ???

Data modeling issue and loop

Tue, 21 Dec 2010 13:58:34 GMT

Hello!

Thank you for your post.

Well I resolved this issue doing with the "Concatenate method"... So I concatenated the tables STK and SALES as if the fields "SALES" and "STOCK_VALUES" would be the same called "VALUES". Then I added a field named 'FLAG' where the firsts group of datas would be flaged as 'SALES' and the second one as 'STK'. Then, I have done a classic star scheme...

I did the the same for the two tables ETB and DEP.

Then this is working!

Thanks a lot for your help.