topic Re: Help : Two tables to one table in QlikView

Help : Two tables to one table

Anonymous — Thu, 07 Mar 2013 15:21:41 GMT

Hello

I have 2 tables :

QV_RET_CHAMBRES_DISPO.QVD with the columns :

RESIDENCE_NUM

PRODUIT_NOM

TARIF

where there is several records per RESIDENCE_NUM

QV_RET_DEPENDANCE_VW.QVD with the columns :

RESIDENCE_NUM

GIR_1_2

GIR_3_4

GIR_5_6

where there is one record per RESIDENCE_NUM

I want to load in QV a unique table with the columns :

RESIDENCE_NUM

PRODUIT_NOM

TARIF

TARIF_GIR = (TARIF+GIR_5_6)*30.5

How do I have to write the sript for this ?

Thanks a lot

Laurent

Re: Help : Two tables to one table

VishalWaghole — Fri, 08 Mar 2013 08:11:51 GMT

Edit your Script like this and find attachmnet :

QV_RET_DEPENDANCE_VW:

LOAD * Inline [
RESIDENCE_NUM,     GIR_1_2,     GIR_3_4,     GIR_5_6
001,               111,          222,          333,
002,               444,          555,          666];

Join

QV_RET_CHAMBRES_DISPO:
LOAD * Inline [
RESIDENCE_NUM,      PRODUIT_NOM,      TARIF
001,               100,               1000,
001,               200,               2000,
001,               300,               3000,
002,               200,               9000,
002,               400,               2500
];

FINAL_QV_RET_DEPENDANCE_VW:
LOAD RESIDENCE_NUM,
PRODUIT_NOM,
TARIF,
GIR_5_6,
(TARIF + GIR_5_6)*30.5 as TARIF_GIR

Resident QV_RET_DEPENDANCE_VW;

DROP Table QV_RET_DEPENDANCE_VW;

Thanks

Vishal

Re: Help : Two tables to one table

Anonymous — Fri, 08 Mar 2013 08:39:40 GMT

Thank you for your help

I have tried to adapt your script (because I want the final table will be QV_RET_CHAMBRES_DISPO and not QV_RET_DEPENDANCE), but it doesn't work well

I have attached my document in this message

Could you help me to know why it does no work.

I want to have finally 2 tables :

QV_RET_RESIDENCES

and

QV_RET_CHAMBRES_DISPO_FINAL which is the concatenation of the 2 tables QV_RET_CHAMBRES_DISPO and QV_RET_DEPENDANCE

Thanks a lot

laurent

Re: Help : Two tables to one table

VishalWaghole — Fri, 08 Mar 2013 09:17:20 GMT

Please try this one.

Here i attached QVW file.

Thanks

Vishal

Re: Help : Two tables to one table

Anonymous — Fri, 08 Mar 2013 09:25:24 GMT

Hi Vishal

It desn't work

I have the message 'Syntax error : FROM missing....'

Thanks

Re: Help : Two tables to one table

VishalWaghole — Fri, 08 Mar 2013 09:34:09 GMT

Hi,

Yes Its exactly right.

Syntax error because you joining two table but you didnt mentioned

from where you pick up DATA for table

QV_RET_CHAMBRES_DISPO and

QV_RET_DEPENDANCE_VW .

see..

QV_RET_CHAMBRES_DISPO:
LOAD * Inline[
RESIDENCE_NUM,
STATUT_RESIDENCE,
PRODUIT_NOM,
PRODUIT_CAT_NUM,
PRODUIT_CAT,
PRODUIT_TYPE_NUM,
PRODUIT_TYPE,
PRODUIT_SOUS_TYPE_NUM,
PRODUIT_SOUS_TYPE,
PLACES,
PLACES_ASH,
VALIDITE_DEBUT,
TARIF,
DATE_MAJ_DISPOS
];

You are created this as your Inline Data, which is also syntacticaly wrong.

so please refer QVW file which i provided you as earlier.

Re: Help : Two tables to one table

Anonymous — Fri, 08 Mar 2013 09:41:45 GMT

Sorry, but I don't understand

You also don't have instruction from in the application that you have sent to me.

Thanks

Re: Help : Two tables to one table

VishalWaghole — Fri, 08 Mar 2013 09:54:06 GMT

Hi,

As per your requirement i created dummy data with this two table

named as QV_RET_DEPENDANCE_VW and QV_RET_CHAMBRES_DISPO.

QV_RET_DEPENDANCE_VW:

LOAD * Inline [
RESIDENCE_NUM,     GIR_1_2,     GIR_3_4,     GIR_5_6            /* This line is the you tables column name */
001,               111,          222,          333,                                                         /*from this line is data for table with respect to column name lable on header */
002,               444,          555,          666];

Join

QV_RET_CHAMBRES_DISPO:
LOAD * Inline [
RESIDENCE_NUM,      PRODUIT_NOM,      TARIF                 /* This line is the you tables column name */
001,               100,               1000,                                                                /* from this line is data for table with respect to column name lable on header */
001,               200,               2000,
001,               300,               3000,
002,               200,               9000,
002,               400,               2500
];

FINAL_QV_RET_DEPENDANCE_VW:
LOAD RESIDENCE_NUM,
PRODUIT_NOM,
TARIF,
GIR_5_6,
(TARIF + GIR_5_6)*30.5 as TARIF_GIR

Resident QV_RET_DEPENDANCE_VW;

DROP Table QV_RET_DEPENDANCE_VW;

Stil if you have doubt then elaborate me.

Thanks

Re: Help : Two tables to one table

Anonymous — Sun, 10 Mar 2013 07:00:39 GMT

Thanks a lot