Help : Two tables to one table

[Anonymous]

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

[VishalWaghole]

Hi

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

[Anonymous]

Hi

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

[VishalWaghole]

Please try this one.

Here  i attached QVW file.

Thanks

Vishal

[Anonymous]

Hi Vishal

It desn't work

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

Thanks

[VishalWaghole]

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.

[Anonymous]

Hi

Sorry, but I don't understand

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

Thanks

[VishalWaghole · Accepted solution]

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

[Anonymous]

Thanks a lot