Generic load - does not create the new table

[Anonymous]

Hello,

I have detected  a bug in a Generic LOAD statement, and I haven't seen other discussions on this matter.

I have a table which contains columns with values (LIBELLEHIERARCHIE1) and columns with column names (LIBELLETYPEHIERARCHIE1).

I need to transform the table in order to create columns with the correct names (LIBELLETYPEHIERARCHIE1) containing rows with values.

I have 6 columns of this type.

To do this I use 6 times the Generic LOAD statement.

Here is my code :

// Creation of a giant table containing 6 columns of values and 6 columns of column names.

Directory;

GenericHIERARCHIEWBS02 :

LOAD IDWBS,

     Lookup('CODENATOPE', 'IDNATOPE', IDNATOPE, 'NATOPE02') as CODENATOPE,

     Lookup('LIBELLENATOPE', 'IDNATOPE', IDNATOPE, 'NATOPE02') as LIBELLENATOPE, 

     ApplyMap('LIBELLEHIERARCHIE02', HIERARCHIE_1, 'SANS HIERARCHIE') as LIBELLEHIERARCHIE1,

     ApplyMap('LIBELLEHIERARCHIE02', HIERARCHIE_2, 'SANS HIERARCHIE') as LIBELLEHIERARCHIE2,

     ApplyMap('LIBELLEHIERARCHIE02', HIERARCHIE_3, 'SANS HIERARCHIE') as LIBELLEHIERARCHIE3,

     ApplyMap('LIBELLEHIERARCHIE02', HIERARCHIE_4, 'SANS HIERARCHIE') as LIBELLEHIERARCHIE4,

     ApplyMap('LIBELLEHIERARCHIE02', HIERARCHIE_5, 'SANS HIERARCHIE') as LIBELLEHIERARCHIE5,

     ApplyMap('LIBELLEHIERARCHIE02', HIERARCHIE_6, 'SANS HIERARCHIE') as LIBELLEHIERARCHIE6,

     If(not IsNull(ApplyMap('IDHIERARCHIE02', HIERARCHIE_1)), ApplyMap('IDHIERARCHIE02', HIERARCHIE_1)) as LIBELLETYPEHIERARCHIE1,

     If(not IsNull(ApplyMap('IDHIERARCHIE02', HIERARCHIE_2)), ApplyMap('IDHIERARCHIE02', HIERARCHIE_2)) as LIBELLETYPEHIERARCHIE2,

     If(not IsNull(ApplyMap('IDHIERARCHIE02', HIERARCHIE_3)), ApplyMap('IDHIERARCHIE02', HIERARCHIE_3)) as LIBELLETYPEHIERARCHIE3,

     If(not IsNull(ApplyMap('IDHIERARCHIE02', HIERARCHIE_4)), ApplyMap('IDHIERARCHIE02', HIERARCHIE_4)) as LIBELLETYPEHIERARCHIE4,

     If(not IsNull(ApplyMap('IDHIERARCHIE02', HIERARCHIE_5)), ApplyMap('IDHIERARCHIE02', HIERARCHIE_5)) as LIBELLETYPEHIERARCHIE5,

     If(not IsNull(ApplyMap('IDHIERARCHIE02', HIERARCHIE_6)), ApplyMap('IDHIERARCHIE02', HIERARCHIE_6)) as LIBELLETYPEHIERARCHIE6          

FROM

[$(RepertoireQVDBRUT)HIERARCHIEWBS01.qvd]

(qvd);

// Creation of 6 sub tables. Each table contains the key made of 3 attributes (IDWBS, CODENATOPE, LIBELLENATOPE)

// the column name (LIBELLETYPEHIERARCHIE1) and the column values (LIBELLEHIERARCHIE1)

GenericSubTableHIERARCHIEWBS02 :

Generic LOAD IDWBS, CODENATOPE, LIBELLENATOPE, LIBELLETYPEHIERARCHIE1, LIBELLEHIERARCHIE1 Resident GenericHIERARCHIEWBS02;

GenericSubTableHIERARCHIEWBS02 :

Generic LOAD IDWBS, CODENATOPE, LIBELLENATOPE, LIBELLETYPEHIERARCHIE1, LIBELLEHIERARCHIE1 Resident GenericHIERARCHIEWBS02;

GenericSubTableHIERARCHIEWBS02 :

Generic LOAD IDWBS, CODENATOPE, LIBELLENATOPE, LIBELLETYPEHIERARCHIE2, LIBELLEHIERARCHIE2 Resident GenericHIERARCHIEWBS02;

GenericSubTableHIERARCHIEWBS02 :

Generic LOAD IDWBS, CODENATOPE, LIBELLENATOPE, LIBELLETYPEHIERARCHIE3, LIBELLEHIERARCHIE3 Resident GenericHIERARCHIEWBS02;

GenericSubTableHIERARCHIEWBS02 :

Generic LOAD IDWBS, CODENATOPE, LIBELLENATOPE, LIBELLETYPEHIERARCHIE4, LIBELLEHIERARCHIE4 Resident GenericHIERARCHIEWBS02;

GenericSubTableHIERARCHIEWBS02 :

Generic LOAD IDWBS, CODENATOPE, LIBELLENATOPE, LIBELLETYPEHIERARCHIE5, LIBELLEHIERARCHIE5 Resident GenericHIERARCHIEWBS02;

GenericSubTableHIERARCHIEWBS02 :

Generic LOAD IDWBS, CODENATOPE, LIBELLENATOPE, LIBELLETYPEHIERARCHIE6, LIBELLEHIERARCHIE6 Resident GenericHIERARCHIEWBS02;

The code in red is 2 times exactly the same statement. If I write the statement only one time, the table is not created.

OK, my code works like this, but I'm wondering if I have done something wrong or is there a bug in the Generic LOAD statement ?

Thanks in advance for your answers.

Tanja

[evan_kurowski]

First, looking over the syntax, you could probably shorten all 6 repetitions of the following:

FROM: If(not IsNull(ApplyMap('IDHIERARCHIE02', HIERARCHIE_1)), ApplyMap('IDHIERARCHIE02', HIERARCHIE_1)) as LIBELLETYPEHIERARCHIE1,

TO: ApplyMap('IDHIERARCHIE02', HIERARCHIE_1, Null()) AS LIBELLETYPEHIERARCHIE1,

Then, if you are making 6 passes at attaching a single field to a 3-field compound key, maybe using GENERIC isn't necessary.

[KEYS]: NOCONCATENATE LOAD IDWBS, DISTINCT, CODENATOPE RESIDENT [GenericHIERARCHIEWBS02];

FOR i = 1 to 6   

Let vCurrentFieldName = PEEK('GenericHIERARCHIEWBS02',0,'LIBELLETYPEHIERARCHIE$(i)');

LEFT JOIN (KEYS)
     LOAD IDWBS, DISTINCT, CODENATOPE,     [LIBELLEHIERARCHIE$(i)] AS [$(vCurrentFieldName)] RESIDENT      [GenericHIERARCHIEWBS02];
NEXT   

Or even if you keep the generic go to:

[GenericSubTableHIERARCHIEWBS02]:
FOR i = 1 to 6
     Generic LOAD IDWBS, CODENATOPE, LIBELLENATOPE, [LIBELLETYPEHIERARCHIE$(i)], [LIBELLEHIERARCHIE$(i)]      Resident GenericHIERARCHIEWBS02;
NEXT

But if you have a significant amount of field names packed into LIBELLETYPEHIERARCHIE1 - 6, then I expect your result is a pretty significant star schema.

[Anonymous]

Hello Evan,

Thank you for your reply. It was very helpful and it simplified my script.

I tried the solution :

HIERARCHIEWBS02:

NOCONCATENATE LOAD IDWBS, CODENATOPE, LIBELLENATOPE RESIDENT GenericHIERARCHIEWBS02;

FOR i = 1 to 6 

FOR j = 0 to NoOfRows('GenericHIERARCHIEWBS02')-1

Let vCurrentFieldName = PEEK('LIBELLETYPEHIERARCHIE$(i)', j, 'GenericHIERARCHIEWBS02');

IF not IsNull($(vCurrentFieldName)) then

EXIT For

ENDIF

NEXT

TRACE vCurrentFieldName $(vCurrentFieldName);

LEFT JOIN

     LOAD IDWBS, CODENATOPE, LIBELLENATOPE, [LIBELLEHIERARCHIE$(i)] AS [$(vCurrentFieldName)] RESIDENT GenericHIERARCHIEWBS02;

NEXT   

I needed to add the 2nd FOR loop, because sometimes the value of the label is NULL.

But when I recharged the script, it blocked during the 2nd passage through the loop without any error message (it is maybe space consuming and my computer can't handle it)...

So I tried the "generic" solution :

FOR i = 1 to 6

GenericSubTableHIERARCHIEWBS02 :

     Generic LOAD IDWBS, CODENATOPE, LIBELLENATOPE, [LIBELLETYPEHIERARCHIE$(i)], [LIBELLEHIERARCHIE$(i)]      Resident GenericHIERARCHIEWBS02;

NEXT

and it worked OK.

[evan_kurowski]

Hello Tanja,
I tested a simulation of the LEFT JOIN method and perhaps a component that was missing was a WHERE clause that drops all join entries where the target field is null.  (i.e. WHERE Len(Trim([LIBELLEHIERARCHIE$(I)])) > 0  )

You can see my simulation and results in the image below:

When I don't use the aforementioned WHERE clause, my example creates 64 rows of data (with plenty of null pockets), however when the WHERE clause is activated, it drops all rows where the target field is blank and the load produces one single "unified" row, kind of "accordion" collapsing the data spread out over the original table.

[Anonymous]

Hello Evan,

I have tried out your code by adjusting it a little bit and it works :

//load the new table HIERARCHIEWBS02

HIERARCHIEWBS02: 

NoConcatenate LOAD Distinct IDWBS, CODENATOPE, LIBELLENATOPE RESIDENT GenericHIERARCHIEWBS02;

LOAD Distinct IDWBS, CODENATOPE, LIBELLENATOPE RESIDENT GenericHIERARCHIEWBS02;

FOR i = 1 to 6 

// a loop to read the column names (F1 to F6)

FOR j = 0 to NoOfRows('GenericHIERARCHIEWBS02')-1

Let vCurrentFieldName = PEEK('LIBELLETYPEHIERARCHIE$(i)', j, 'GenericHIERARCHIEWBS02');

IF not IsNull($(vCurrentFieldName)) then

EXIT For

ENDIF

NEXT

LEFT JOIN (HIERARCHIEWBS02)

     LOAD IDWBS, CODENATOPE, LIBELLENATOPE, LIBELLEHIERARCHIE$(i) as $(vCurrentFieldName) RESIDENT GenericHIERARCHIEWBS02

     Where Len(Trim([LIBELLEHIERARCHIE$(i)])) > 0;

NEXT   

And here is the result :

As you can see, at the beginning of the code, I still need to LOAD the table HIERARCHIEWBS02 twice.

I have tried by changing the attribute names and put the NoConcatenate, but the new table still isn't being created.

So at least now se know that this is not related to the generic tables.

[evan_kurowski]

The end result should be the same but load performance times would improve if you switch from row-by-row searching to full table passes, in order to extract the field names.

[PLUCK_FIELDNAME_$(i)]:
NOCONCATENATE FIRST 1 LOAD [F$(i)] AS [ISOLATED_FIELD_NAME$(i)] RESIDENT [GENERIC_SOURCE]
WHERE LEN(TRIM(F$(i)))>0;

Let vCurrentFieldName = Peek('ISOLATED_FIELD_NAME$(i)',0,'PLUCK_FIELDNAME_$(i)');

[Anonymous]

Hello Evan,

Thanks for the advice, it works great !

Tanja