topic Creating tables in Qlikview in QlikView

Creating tables in Qlikview

Mon, 06 Sep 2010 08:08:45 GMT

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.

Creating tables in Qlikview

Miguel_Angel_Baeyens — Mon, 06 Sep 2010 08:16:13 GMT

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.

Re. :Re: Creating tables in Qlikview

Mon, 06 Sep 2010 08:42:16 GMT

Please, i don't hear very well.

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

Thanks

Re. :Re: Creating tables in Qlikview

Miguel_Angel_Baeyens — Mon, 06 Sep 2010 08:50:29 GMT

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;

Re. :Re: Re. :Re: Creating tables in Qlikview

Mon, 06 Sep 2010 10:19:45 GMT

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.

Re. :Re: Re. :Re: Creating tables in Qlikview

Miguel_Angel_Baeyens — Mon, 06 Sep 2010 10:49:32 GMT

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.

Re. :Re: Re. :Re: Re. :Re: Creating tables in Qlikview

Mon, 06 Sep 2010 11:17:33 GMT

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.

Re. :Re: Re. :Re: Re. :Re: Creating tables in Qlikview

Miguel_Angel_Baeyens — Mon, 06 Sep 2010 11:45:37 GMT

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.

Re. :Re: Re. :Re: Re. :Re: Re. :Re: Creating tables in Qlikview

Mon, 06 Sep 2010 14:33:32 GMT

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

Re. :Re: Re. :Re: Re. :Re: Re. :Re: Creating tables in Qlikview

Miguel_Angel_Baeyens — Mon, 06 Sep 2010 15:09:10 GMT

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?

Re. :Re: Re. :Re: Re. :Re: Re. :Re: Re. :Re: Creating tables in Qlikview

Mon, 06 Sep 2010 18:55:18 GMT

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

Re. :Re: Re. :Re: Re. :Re: Re. :Re: Re. :Re: Creating tables in Qlikview

Miguel_Angel_Baeyens — Mon, 06 Sep 2010 21:46:04 GMT

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.

Re. :Re: Re. :Re: Re. :Re: Re. :Re: Re. :Re: Re. :Re: Creating tables in Qlikview

Tue, 07 Sep 2010 08:24:45 GMT

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

Re. :Re: Re. :Re: Re. :Re: Re. :Re: Re. :Re: Re. :Re: Creating tables in Qlikview

Miguel_Angel_Baeyens — Tue, 07 Sep 2010 08:56:50 GMT

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

Re. :Re: Re. :Re: Re. :Re: Re. :Re: Re. :Re: Re. :Re: Re. :Re: Creating tables in Qlikview

Tue, 07 Sep 2010 12:16:38 GMT

Thank MIGUEL, i have seen the problem.

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