Create TABLE with the GENERIC LOAD

[ab92]

Good morning Everyone,

I wish you the best for this year !

After to test this tuto : Use cases for Generic Load | Qlikview Cookbook

I understand the function of the Generic Load thanks Cookbook for this help ^^, however I would like to create specific table (join some table after the Generic Load) .

For a specific need I want to join two tables in my application, but with this post actually propose to join all the tables.

Find below the script :

FOR i = NoOfTables()-1 to 0 STEP -1

  LET vTable=TableName($(i));

  IF WildMatch('$(vTable)', 'Flags.*') THEN

    LEFT JOIN (OrdersTable) LOAD * RESIDENT    [$(vTable)];

    DROP TABLE  [$(vTable)];

  ENDIF

NEXT i

But I tried my best to improve this script but with my skills in Qlikview it's complicate, please someone can help me ?

I would like to join in this case only two table from the link Cookbook example :

Many thanks for your help,

Axel

[pradosh_thakur]

Try this

FOR i = NoOfTables()-1 to 0 STEP -1

  LET vTable=TableName($(i));

  IF WildMatch('$(vTable)', 'Flags.Status_Shipped','Flags.Status_Returned') THEN

    LEFT JOIN (OrdersTable) LOAD * RESIDENT    [$(vTable)];

    DROP TABLE  [$(vTable)];

  ENDIF

NEXT i

inside the wild match put the table name you want to join others will be as it is ..

[ab92]

Thank you so much Pradosh for your help, your script is working I have this result :

But it is possible to create a specific table like in this example it definitely not useful but for my application yes .. 

Sorry for my request, I hope the second is more explanatory, It is possible to join the informations of status.cancelled and status_retunred in one table that we call case1 and the other in one table that we call case2 ?

Many thanks for your reactivity Pradosh,

Axel

[pradosh_thakur]

Hi

to avoid the confusion. you want

1: join status.cancelled and status_retunred  to case1

2: others to case 2

[ab92]

Only the informations inside the table not necessary to join the OrdersTable...

Please find below an idea about that I want: each case is equal at one table.

Many thanks for your help

[pradosh_thakur]

I have done something similar to this and its working

Plese check the table names spelling inside wildmatch

noconcatenate

OrdersTable1:

load * resident OrdersTable;

noconcatenate

OrdersTable2:

load * resident OrdersTable;

FOR i = NoOfTables()-1 to 0 STEP -1

  LET vTable=TableName($(i));

  IF WildMatch('$(vTable)', 'Flags.Status_Shipped','Flags.Status_Returned') THEN

    LEFT JOIN (OrdersTable) LOAD * RESIDENT    [$(vTable)];

// DROP TABLE  [$(vTable)];

  ENDIF

IF WildMatch('$(vTable)', 'Flags.Status_Cancelled','Flags.Status_Returned') THEN

    LEFT JOIN (OrdersTable1) LOAD * RESIDENT    [$(vTable)];

ENDIF

IF WildMatch('$(vTable)', 'Flags.Status_Shipped','Flags.Status_Active') THEN

    LEFT JOIN (OrdersTable2) LOAD * RESIDENT    [$(vTable)];

ENDIF

NEXT i

FOR j = NoOfTables()-1 to 0 STEP -1

  LET vTableName1=TableName($(j));

  IF WildMatch('$(vTableName1)','Flags.*') then

  DROP TABLE  [$(vTableName1)];

  ENDIF

  NEXT j

[ab92]

Good afternoon Pradosh,

Many thanks for your help, sorry for the delay...

I tested your script and please find below the view of the table :

I would be possible to delete the synthetic key ?

Many thanks for your help ...

[Peter_Cammaert · Accepted solution]

Use this script code to group tables together. Put it after the GENERIC LOAD. The SUB can appear anywhere, as long as it isn't hidden in another SUB and it is defined before your first CALL:

SUB Group2Tables(vTarget, vSource1, vSource2)

  [$(vTarget)]:

  NOCONCATENATE

  LOAD * RESIDENT [$(vSource1)];

  JOIN ([$(vTarget)])  // Perform an OUTER (Natural) JOIN

  LOAD * RESIDENT [$(vSource2)];

  DROP Tables [$(vSource1)], [$(vSource2)]; // Drop source tables

END SUB

CALL Group2Tables('CASE 1', 'Flags.Status_Cancelled', 'Flags.Status_Returned');

CALL Group2Tables('CASE 2', 'Flags.Status_Active', 'Flags.Status_Shipped');

You may also want to use better names for the target tables.

Best,

Peter

[ab92]

Hi Peter,

Really I don't know how can I say.. but thank you so much for your help Peter, it's amazing job and very effective !

I'm working on this problem since many time...

Best regards,

Axel

[Peter_Cammaert]

Glad I could help.

Enjoy QlikView and the Qlik Community !

Best,

Peter