topic Re: Create only missing values in a table in App Development

Create only missing values in a table

DEMONIO_AZUL — Mon, 28 Oct 2024 22:03:05 GMT

Hello there!

Is there a way to load a table with a load script and then append the missing values using another script section that creates them?

This is what I mean:

The original table is something like this:

Location	Code
Madrid	A0
Barcelona	A1
Toledo	A2
London	B3
Paris	C2
Osaka	D5

But I need to append to the table above all values that are missing, namely:

for letter A, from A3 to A9;
for letter B, from B0 to B2 and from B4 to B9;
for letter C, from C0 to C1 and from C3 to C9;
for letter D, from D0 to D4 and from D6 to D9; and
for letter E, from E0 to E9 (because it is still not used).

For all appended Codes, the location may be filled with a blank or "not used".

Thanks in advance for all your help.

Re: Create only missing values in a table

Qrishna — Tue, 29 Oct 2024 07:16:28 GMT

As long as your both tables' field names and number of fields match, the latter table automatically gets appended to the former table: try below:

ExistingData:
load * inline [
Location, Code
Madrid, A0
Barcelona,A1
Toledo, A2
London, B3
Paris, C2
Osaka, D5
];

AppendData:
Load * inline [
Location, Code
,A3
,A4
,A5
,A6
,A7
,A8
,A9
,B4
,B5
,C3
,C4
,D6
,D7
,E0
,E1
];

before:

After:

Re: Create only missing values in a table

DEMONIO_AZUL — Tue, 29 Oct 2024 10:02:34 GMT

Thanks, Qrishna. This works for the example table that I provided.The sample table is a simplified one where I tried to explain the situation without complicated structures.

My bad, I was not clear enough. While I included the word “create” at this question’s title, I was not specific in telling these relevant topics:

1. I may get a table with hundreds of rows and the field Code may be A000 to A999.

2. I won’t know which rows are missing.
3. Due to 1 & 2, it is impossible to input manually a table with the missing rows.
4. Thus, I need to create automatically at load script the table to be appended. That table should have the missing rows.

Hope this is clear. And hopefully you can help me further.

Thanks again!

Re: Create only missing values in a table

Kushal_Chawda — Tue, 29 Oct 2024 10:58:11 GMT

@DEMONIO_AZUL So when you Say A3 to A9 means, A003 to A999? Could you be specific?

Re: Create only missing values in a table

DEMONIO_AZUL — Tue, 29 Oct 2024 12:53:26 GMT

Yes, Kushal, that's correct.

The current table has three digits in the Code field:

Location	Code
Madrid	A900
Barcelona	A107
Toledo	A201
London	B327
Paris	C255
Osaka	D566

Obviously, this table is a small set of a bigger file.

So I need to generate automatically all missing codes for all letters.

E.g. for letter A, I need to generate codes in the load script from A000 to A106, A108 to A200, A202 to A899 and A901 to A999.

Similar for all other letters, even for letter E, which should be generated from E000 to E999 even if there is no Code in the input file with that letter.

Re: Create only missing values in a table

Kushal_Chawda — Tue, 29 Oct 2024 13:15:32 GMT

@DEMONIO_AZUL try below. For all not existing codes location will be NULL. If you want some values instead, you can take resident of this table create a column with below condition

if(len(trim(Location))=0,'Not defined',Location) as Location_Final

Data:
Load * Inline [
Location, Code
Madrid, A900
Barcelona, A107
Toledo, A201
London, B327
Paris, C255
Osaka, D566
];

Concatenate(Data)
Load *
where not Exists(Code);
Load Letter & num(From + IterNo()-1,'000') as Code
while IterNo()-1<=To;
Load * Inline [
Letter,From,To
A,0,999
B,0,999
C,0,999
D,0,999
E,0,999 ];

Re: Create only missing values in a table

DEMONIO_AZUL — Tue, 29 Oct 2024 13:29:34 GMT

THANK YOU, KUSHAL!
This is exactly what I needed!! Much appreciated!

Re: Create only missing values in a table

seanbruton — Fri, 01 Nov 2024 15:47:26 GMT

Superb !!! Neat Solution.