Creating tables in Qlikview

[#1]

Hi community,

Please, I would like to create a table by copying another table. Keeping the same structure and the same data.

Example:

Here is the script to create my table:

Bilan_Actif:
LOAD CodeLigne,
Money (SUM( if( (TypeCompte='B' and Annee = '2010' and (IsNull(CodeLigne)=0) ), MontantDebit) ), '# ##0,00;-# ##0,00') as brutn,
Money (SUM( if( (TypeCompte='B' and Annee = '2009' and (IsNull(CodeLigne)=0) ), MontantDebit) ), '# ##0,00;-# ##0,00' ) as brutnm,
Money (SUM( if( (TypeCompte='A' and Annee = '2010' and (IsNull(CodeLigne)=0) ), MontantCredit) ), '# ##0,00;-# ##0,00' ) as amorn,
Money (SUM( if( (TypeCompte='A' and Annee = '2009' and (IsNull(CodeLigne)=0) ), MontantDebit) ), '# ##0,00;-# ##0,00' ) as amornm
RESIDENT Balance
GROUP BY CodeLigne;
LEFT JOIN LOAD CodeLigne,
LibelleLigne,
NoLT
RESIDENT Ligne;

It's OK.

I would like to create another table named Bilan_Actif_Bis that has different field names, but keeps the same data as the other table Bilan_Actif.

Thanks you in advance.

[#2]

When i launche the reloading, the script error box dialog displays with the following message:

brutNiv02,
Bilan_Actif_Bis:
LOAD CodeLigne,
brutn AS brutn_Bis,
brutnm AS brutnm_Bis,
amorn AS amorn_Bis,
amornm AS amornm_Bis,
LibelleLigne,
NoLT,
CodeSsSousGroupe AS CodeSsSousGroupe_Bis,
CodeSousGroupe AS CodeSousGroupe_Bis,
CodeGroupe AS CodeGroupe_Bis,
CodePere AS CodePere_Bis,
SUM (If ( (Not IsNull(CodeGroupe) and Not IsNull(CodeSousGroupe) and IsNull(CodeSsSousGroupe) and Not IsNull(CodePere) ), brutn )) AS brutNiv02
RESIDENT Bilan_Actif
GROUP BY CodeLigne

[Miguel_Angel_Baeyens]

Hi,

Rename

LibelleLigne AS LibelleLigne_Bis, NoLT AS NoLT_Bis

Apart from that and to add all fields to the group by clause, will probably make clear where the problem is, as if you just set

1 AS brutNiv02

just for testing should get no errors loading.

Regards.

[#4]

When i use:

1 AS brutNiv02

Everythings goes well

But i want to use this SUM aggregation.

I don't know really what is the problem.

Thank you for your help

[Miguel_Angel_Baeyens]

Hello Yimen,

I'm sorry to insist, but I still think the problem is that you need to group by all fields except for the new one:

Bilan_Actif_Bis:LOAD CodeLigne AS CodeLigne_Bis, brutn AS brutn_Bis, brutnm AS brutnm_Bis, amorn AS amorn_Bis, amornm AS amornm_Bis, LibelleLigne AS LibelleLigne_Bis, NoLT AS NoLT_Bis, CodeSsSousGroupe AS CodeSsSousGroupe_Bis, CodeSousGroupe AS CodeSousGroupe_Bis, CodeGroupe AS CodeGroupe_Bis, CodePere AS CodePere_Bis, SUM (If((Not IsNull(CodeGroupe) and Not IsNull(CodeSousGroupe) and IsNull(CodeSsSousGroupe) and Not IsNull(CodePere)), brutn)) AS brutNiv02RESIDENT Bilan_ActifGROUP BY CodeLigne, brutn, brutnm, amorn, amornm, LibelleLigne, NoLT, CodeSsSousGroupe, CodeSousGroupe, CodeGroupe, CodePere

Note that SUM in this case will only return the same value of brutn for those records that have those Codes in the conditional, but it will not group the value for brutn for all records loaded.

Hope that helps

[#6]

Thank MIGUEL, i have seen the problem.

The GROUP BY clause must have the others fields that not enter in the agregation.