Table missing after creating them in do while loop

[LiquidSword]

The table CrossSales_Status_Result is missing after creating it in do while loop. I want to create a table referencing it in [Customer_List_Existing] after exiting the loop. 

Set a=0;

LET vCount = NoOfRows('Customer_List');

Do while a< $(vCount);

(....)

NoConcatenate
CS_Temp10:
Load *,
Customer_Code & '_' & MonthYear as CrossSales_Key,
Customer_Code & '_' & MonthYear as Customer_Key;
Load Customer_Code,
MonthYear,
CrossSales_Status,
Date(Date#(CrossSales_Date, 'YYYYMMDD'), 'D/M/YYYY') as CrossSales_Date;
Resident CS_Temp9;

Drop Table CS_Temp9;

CrossSales_Status_Result:
Load *,
Cross_Cate & '_' & CrossSales_Key as CrossSales_Key_v2;
Load Distinct Customer_Code,
CrossSales_Date,
CrossSales_Key,
Replace(Replace(Replace(Replace(CrossSales_Status,'+Promotion',''), '+promotion', ''), '->Promotion', ''), '->promotion','') as CrossSales_Status,
SubField(CrossSales_Status , '->', 2) as Cross_Cate
Resident CS_Temp10
where NOT ISNULL(CrossSales_Status);


Drop Table CS_Temp10;


CS_Temp11:
Load Distinct CrossSales_Date
Resident CrossSales_Status_Result;

Join (CS_Temp11)

CS_Temp12:
Load Distinct CrossSales_Status
Resident CrossSales_Status_Result;


Concatenate (CrossSales_Status_Result)
ALSKDLS:
Load CrossSales_Date,
CrossSales_Status,
1 & '_' & date(MonthEnd(CrossSales_Date), 'MMM-YYYY') as CrossSales_Key,
1 as Customer_Code
Resident CS_Temp11;

(...)

Let a=a+1;

Loop


Customer_List_Existing:
Load Customer_Code as Cust_ID
Resident CrossSales_Status_Result;

[Vegar]

Are you sure that it is missing? or is it just not containing the data that you expect?

You have some (...) are they manipulating the `CrossSales_Status_Result` table in any way. If so it could be that you don't get your expected autoconcatenate of `CrossSales_Status_Result` to work as expected in the beginning of your loop. 

Steps to troubleshoot.

- Try to add an exit script after the loop to see what's in your datamodel 

- Try to add an exit script at the end of the loop. The script stops at the end of first iteration. Check what tables and fields that are in your datamodel. 

[LiquidSword]

hi, (...) is used to replace paragraphs of script.

Anyways, I added the following inline table in the loop (random table just for testing) and exit script after the loop, and the table is missing.

Table:

LOAD * Inline [

ID  , brand , criteria ,    assesement

123 , abc ,   Q9,                   Y

145 , abc ,   Q9,                   Y

123 , abc ,   P11,                  Y

145 , abc ,   P11,                  N

];

I then tried adding exit script between "Let a=a+1;" and "Loop" and also after "Loop". [Table] is still missing from data model.

[LiquidSword]

This is the entire segment:

NoConcatenate

Customer_List_Temp:

Load Distinct Customer_Code, Item_Code

Resident Item_Sales

Where Item_Amount <> '0'

and ( NUM(Transaction_Date) >= Num(MonthStart(AddMonths(Today(), -3))) and NUM(Transaction_Date) <= Num(MonthEnd(AddMonths(Today(), -1))) )

//and ( NUM(Transaction_Date) >= Num(MonthStart(AddMonths(Today(), -1))) and NUM(Transaction_Date) <= Num(MonthEnd(AddMonths(Today(), -1))) )

;

left join (Customer_List_Temp)

Load Item_Code, Master_Category

Resident Item_Master;

NoConcatenate

Customer_List:

Load Distinct Customer_Code

Resident Customer_List_Temp

where Item_Code <> 'CD' and Not ISNULL(Master_Category)

//and Customer_Code like 'LMC21*';

;

Drop Table Customer_List_Temp;

Set a=0;

LET vCount = NoOfRows('Customer_List'); 

Do while a< $(vCount)

Let vCustomer = peek('Customer_Code',$(a) ,'Customer_List');

CS_Temp0:

Load Customer_Code,

  Transaction_Date,

      Item_Code

Resident Item_Sales

//where Customer_Code = 'LMC14-0102' 

where Customer_Code = '$(vCustomer)'

//or Customer_Code = 'LMC14-0075' or Customer_Code = 'LMC14-0102' or Customer_Code = 'LMC14-0146' or Customer_Code = 'LMC14-0178'

//where Item_Amount <> '0'

and Item_Amount <> '0'

;

Left Join (CS_Temp0)

Load Item_Code,

  Master_Category

Resident Item_Master;

CS_Temp0_1:

Load Customer_Code,

  Date(Num(MonthEnd(Transaction_Date)), 'MMM-YYYY') as MonthYear,

      Transaction_Date,

      Master_Category

Resident CS_Temp0

where Not Master_Category LIKE '*+*';

NoConcatenate

//Qualify*;

CS_Temp0_2:

Load *

Resident CS_Temp0_1

Order By Customer_Code, MonthYear asc;

//Unqualify*;

Drop Table CS_Temp0, CS_Temp0_1;

NoConcatenate

CS_Temp0_3:

Load Customer_Code,

  MonthYear as MonthYear,

      Transaction_Date,

      Master_Category as Category,

  if(Peek(Customer_Code) = Customer_Code, Peek(NodeID)+1, 1) as NodeID

Resident CS_Temp0_2

where NOT ISNULL(Master_Category)

Order by Customer_Code, Transaction_Date asc;

NoConcatenate

CS_Temp0_4:

Load Customer_Code,

//   Customer_Code as Customer,

  MonthYear,

      Transaction_Date,

      Category,

      NodeID,

  if(Peek(Customer_Code) = Customer_Code, Peek(NodeID), '') as ParentID

//      if(Peek(Customer_Code) = Customer_Code, '1', '') as ParentID

Resident CS_Temp0_3

Order by Customer_Code, Transaction_Date asc;

NoConcatenate

CS_Temp0_5:

Load Customer_Code as Customer,

  MonthYear,

      Transaction_Date,

      Category,

      NodeID,

  ParentID

Resident CS_Temp0_4

Order by Customer_Code, Transaction_Date asc;

Drop Table CS_Temp0_2, CS_Temp0_3

, CS_Temp0_4

;

//exit script;

// NoConcatenate

// CS_Temp1:

// Load * inline [

// Customer, MonthYear, NodeID, ParentID, Category, Transaction_Date

// A, 01-2020, 1, , HR, 12/01/2020

// A, 02-2020, 2, 1, FA, 11/02/2020

// A, 03-2020, 3, 2, HR, 13/03/2020

// A, 04-2020, 4, 3, Body, 14/04/2020

// A, 05-2020, 5, 4, FA, 15/05/2020

// B, 01-2020, 1, , FA, 12/01/2020

// B, 01-2020, 2, 1, HR, 13/01/2020

// B, 03-2020, 3, 2, HR, 13/03/2020

// B, 04-2020, 4, 3, FA, 14/04/2020

// B, 05-2020, 5, 4, Body, 15/05/2020

// ];

NoConcatenate

CS_Temp1_1:

Load Customer,

MonthYear,

     Transaction_Date,

     IF(Peek(MonthYear) = MonthYear AND PEEK(Customer) = Customer, '1', '0') as Flag

//Resident CS_Temp1;

//Resident CS_Temp0_4;

Resident CS_Temp0_5;

NoConcatenate

CS_Temp1_2:

Load Customer & MonthYear as JoinKey,

     Sum(Flag) as Flag

Resident CS_Temp1_1

Group By Customer, MonthYear;

Drop Table CS_Temp1_1;

NoConcatenate

CS_Temp2:

Hierarchy (NodeID, ParentID, Category) 

Load

Customer,

    MonthYear,

    Transaction_Date,

    NodeID,

    ParentID,

    Category

//Resident CS_Temp0_4;

Resident CS_Temp0_5;

//Resident CS_Temp1_1;

Drop Table CS_Temp0_5;

//Drop Table CS_Temp0_4;

Drop Fields NodeID, ParentID From CS_Temp2;

CS_Temp3:

CrossTable (CategoryName, CategoryPath, 4) 

Load Customer as Customer_1,

     MonthYear as MonthYear_1,

     Transaction_Date as Transaction_Date_1,

     Category as Category_1,

     *

Resident CS_Temp2;

Drop Table CS_Temp2;

NoConcatenate

CS_Temp4:

Load Customer_1 as Customer,

MonthYear_1 as MonthYear,

     Category_1 as Category,

     Transaction_Date_1 as Transaction_Date,

     KeepChar(CategoryName, '0123456789') as Order,

     CategoryPath

Resident CS_Temp3

Where CategoryName <> 'Customer' AND CategoryName <> 'MonthYear' AND CategoryName <> 'Category' AND CategoryName　<> 'Transaction_Date';

// Qualify*;

// QQQ:

// Load * 

// resident CS_Temp4;

// Unqualify*;

Drop Table CS_Temp3;

NoConcatenate

CS_Temp5:

Load 

//*,

     Customer,

     MonthYear,

 //    Transaction_Date,

 //    Category,

     RowNO() as Order,

     CategoryPath,

     AutoNumberHash128(Customer,MonthYear,CategoryPath) as Key

//     AutoNumberHash128(Customer,MonthYear,Category,CategoryPath) as Key

Resident CS_Temp4

Where Not Exists('Key', AutoNumberHash128(Customer,MonthYear,CategoryPath))

//Where Not Exists('Key', AutoNumberHash128(Customer,MonthYear,Category,CategoryPath))

//and Order = '1'

Order By Customer, MonthYear asc;

//, Transaction_Date asc;

Drop Table CS_Temp4;

// Qualify*;

// QQW:

// Load * 

// resident CS_Temp5;

// Unqualify*;

CS_Temp6:

Load Customer,

MonthYear,

     Concat(distinct CategoryPath , '+', Order) as CategoryPath

Resident CS_Temp5

Group By Customer, MonthYear;

// Qualify*;

// QQE:

// Load * 

// resident CS_Temp6;

// Unqualify*;

Drop Table CS_Temp5;

NoConcatenate

CS_Temp7:

Load Customer,

MonthYear,

     IF(Peek(CategoryPath) = CategoryPath AND Peek(Customer) = Customer, NULL(), CategoryPath) as CategoryPath

Resident CS_Temp6

Order By Customer, MonthYear;

Drop Table CS_Temp6;

NoConcatenate

CS_Temp8:

Load Customer,

MonthYear,

     IF(Left(CategoryPath, 2) = '->', NULL(), CategoryPath) as CategoryPath;

//     Flag,

//     CategoryPath;

Load Customer,

MonthYear,

//     CategoryPath,

//     IF(Index(CategoryPath,'+',-1) = 0, CategoryPath,

      Left(CategoryPath, Index(CategoryPath,'+',-1)-1) & '->' & mid(CategoryPath, Index(CategoryPath,'+',-1)+1) 

//        ) 

        as CategoryPath

Resident CS_Temp7;

Drop Table CS_Temp7;

NoConcatenate

CS_Temp9:

Load *,

Customer & MonthYear as JoinKey,

     AutoNumberHash128(Customer,CategoryPath) as Key

Resident CS_Temp8

Where Not Exists('Key', AutoNumberHash128(Customer,CategoryPath));

Left Join 

Load *

Resident CS_Temp1_2;

Drop Table CS_Temp8, CS_Temp1_2;

NoConcatenate

CS_Temp10:

Load Customer_Code,

MonthYear,

     CrossSales_Status,

CrossSales_Date,

//   Customer_Code & '_' & Num(Status_Date) as Status_Key,

  Customer_Code & '_' & MonthYear as CrossSales_Key,

      Customer_Code & '_' & MonthYear as Customer_Key;   

Load Customer_Code,

  MonthYear,

      CrossSales_Status,

      Date(Date#(CrossSales_Date, 'YYYYMMDD'), 'D/M/YYYY') as CrossSales_Date;

Load Customer_Code,

  MonthYear,

      CrossSales_Status,

      Year1 & Month1 & Day1 as CrossSales_Date;

Load Customer_Code,

  MonthYear,

      CrossSales_Status,

      NUM(SubField(CrossSales_Date, '/', 1), '00') AS "Day1",

      NUM(SubField(CrossSales_Date, '/', 2), '00') AS "Month1",

      NUM(SubField(CrossSales_Date, '/', 3), '00') AS "Year1";

//LOAD Customer as Customer_Code,

LOAD Customer_Code,

MonthYear,

     Date(MonthEnd(Num(MonthYear))) as CrossSales_Date,

     CategoryPath as CrossSales_Status;

Load Customer as Customer_Code,

MonthYear,

     CategoryPath

//     IF(Flag = '1', NULL(), CategoryPath) as CategoryPath

Resident CS_Temp9;

Drop Table CS_Temp9;

STORE Customer_Code,

MonthYear,

     CrossSales_Status,

CrossSales_Date,

CrossSales_Key, Customer_Key From CS_Temp10 INTO [lib://Vantis-Develop:DataFiles/CS_Temp10.csv](txt);

// NoConcatenate

// CS_Temp11:

// Load *

// Resident CS_Temp10;

// Join 

// Load MonthYear as MonthYear_Compare

// Resident MasterCalendar;

// NoConcatenate

// CS_Temp12:

// Load *

// Resident CS_Temp11

// Where Num(MonthEnd(MonthYear)) <= Num(MonthEnd(MonthYear_Compare));

NoConcatenate

CrossSales_Status_Result:

Load *,

Cross_Cate & '_' & CrossSales_Key as CrossSales_Key_v2;

Load Distinct Customer_Code,

CrossSales_Date,

     CrossSales_Key,

     Replace(Replace(Replace(Replace(CrossSales_Status,'+Promotion',''), '+promotion', ''), '->Promotion', ''), '->promotion','') as CrossSales_Status,

     SubField(CrossSales_Status , '->', 2) as Cross_Cate

//      Date(MonthEnd(Num(MonthYear_Compare))) as CrossSales_Date,

//      Customer_Code & '_' & MonthYear_Compare as CrossSales_Key,

//      IF(ISNULL(CrossSales_Status) or CrossSales_Status = '' or CrossSales_Status = '-', 'None', CrossSales_Status) as CrossSales_Status

Resident CS_Temp10

where NOT ISNULL(CrossSales_Status);

Drop Table CS_Temp10;

//Resident CS_Temp12;

// Qualify*;

// PPP:

// Load *

// Resident CrossSales_Status_Result;

// unqualify*;

//Drop Table CS_Temp12, CS_Temp11, CS_Temp10;

//Drop Table CS_Temp10;

NoConcatenate

CS_Temp11:

Load Distinct CrossSales_Date

Resident CrossSales_Status_Result;

Join (CS_Temp11)

CS_Temp12:

Load Distinct CrossSales_Status

Resident CrossSales_Status_Result;

Concatenate (CrossSales_Status_Result)

Load CrossSales_Date,

CrossSales_Status,

     1 & '_' & date(MonthEnd(CrossSales_Date), 'MMM-YYYY')  as CrossSales_Key,

     1 as Customer_Code

Resident CS_Temp11;

Drop table CS_Temp11;

NoConcatenate

Cross_V2:

Load Customer_Code & '_' & date(MonthEnd(Transaction_Date), 'MMM-YYYY') as CrossSales_Key,

  Item_Code,

      Item_Amount

Resident Item_Sales;

Left Join (Cross_V2)

Load Item_Code,

Master_Category

Resident Item_Master;

NoConcatenate

Cross_V3:

Load Distinct

Master_Category & '_' & CrossSales_Key as CrossSales_Key_v2,

Item_Code as Cross_Code,

Item_Amount as Cross_Amount

Resident Cross_V2;

//drop table CS_Temp1;

Table:

LOAD * Inline [

ID  , brand , criteria        ,    assesement

123 , abc ,   Q9,                   Y

145 , abc ,   Q9,                   Y

123 , abc ,   P11,                  Y

145 , abc ,   P11,                  N

];

Let a=a+1;

Loop

Customer_List_Existing:

Load Customer_Code as Cust_ID

Resident CrossSales_Status_Result;

exit script;

[Vegar · Accepted solution]

You write that not even your inline gets generated. 

Are you sure that you ever get into the loop? Do you have rows in your Customer_List table?

Try printing out some validation figures before running the loop by adding the bold section in script snipplet below. Just to check that 

Set a=0;

LET vCount = NoOfRows('Customer_List'); 

trace a=$(a);

trace vCount=$(vCount);

LET loop_condition = a< $(vCount) ; //The same as your do while loop is using
trace loop_condition=$(loop_condition);

Do while a< $(vCount)

   ...

loop

[LiquidSword]

yes, Customer_List is indeed empty! i was stuck on this for so long thinking the answer was from inside the loop. you're brilliant!

[Vegar]

Thanks for the feedback. 👍 Glad I could help. 😊