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.

[Miguel_Angel_Baeyens]

Hello Yimen,

I'd suggest you to rename fields from a resident load

Bilan_Actif_Bis:LOAD CodeLigne AS Codeligne_Bis, brutn as brutn_bis... RESIDENT Bilan_Acttif

Another possibility is to qualify your new table:

QUALIFY *; LOAD * RESIDENT Bilan_Actif; UNQUALIFY *;

Anyway, I'd use the first one.

Regards.

[#3]

Please, i don't hear very well.

Can you write all the script using the script that i give above.

Thanks

[Miguel_Angel_Baeyens]

Renaming fields using "AS"

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_BisRESIDENT Bilan_Actif;

[#5]

Thanks

But i want to add this field

SUM (If ( (IsNull(CodeGroupe)=0 and IsNull(CodeSousGroupe)=0 and IsNull(CodeSsSousGroupe)=-1 and IsNull(CodePere)=0 ), brutn )) AS brutNiv02

When i do it, an error appears :

Script error:

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 ( (IsNull(CodeGroupe)=0 and IsNull(CodeSousGroupe)=0 and IsNull(CodeSsSousGroupe)=-1 and IsNull(CodePere)=0 ), brutn )) AS brutNiv02

RESIDENT Bilan_Actif

I don't know the problem please it's very urgent.

[Miguel_Angel_Baeyens]

Hello Yimen,

This seems to be your mispelling:

SUM (If ( (NOT IsNull(CodeGroupe) and NOT IsNull(CodeSousGroupe) and IsNull(CodeSsSousGroupe) and NOT IsNull(CodePere)), brutn )) AS brutNiv02

IsNull() returns true or false itself, so there's no need to compare. Besides, if you are using a SUM on your script, you have to use GROUP BY clause to get it loaded correctly.

Regards.

[#7]

Yes

I apply what you says as:

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)=0 ), brutn )) AS brutNiv02
RESIDENT Bilan_Actif
GROUP BY CodeLigne;

But the error message always appears:

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)=0 ), brutn )) AS brutNiv02
RESIDENT Bilan_Actif
GROUP BY CodeLigne

Please it's urgent.

[Miguel_Angel_Baeyens]

Hello Yimen,

You still have a Isnull() = 0 in your code. Check the code in bold in your script.

SUM (If ( (Not IsNull(CodeGroupe) and Not IsNull(CodeSousGroupe) and IsNull(CodeSsSousGroupe) and Not IsNull(CodePere) ), brutn ))

Regards.

[#9]

I have removed " =0" but the error message always appears.

[Miguel_Angel_Baeyens]

Yimen,

I see that you have a group by clause. You have to add all your fields except for the one you are aggregating to the group by.

What error is displaying?